CODEWITHSUNDEEP
sqlms-access
kjmerf
kjmerf

Reputation: 4345

How to Pivot Query Results in MS Access?

After running my query, my results are as follows:

user  report   rights
1       a       FULL
1       b       FULL
1       c       FULL
1       d       READ
2       b       FULL
2       c       FULL
3       a       FULL
3       b       READ
3       c       READ

I would like to pivot these results in this format:

user    a       b       c       d
1       FULL    FULL    FULL    FULL
2               READ    FULL        
3       FULL    READ    READ

Basically, I would like each user listed once and display the report names as columns. Then show the rights of the user for each report. I am using MS Access for this query. Please suggest a way to display my query results as shown.

Upvotes: 0

Views: 4106

Answers (1)

Parfait
Parfait

Reputation: 107642

Simply use the crosstab query, a unique command in Jet/ACE SQL:

TRANSFORM Max(t.rights) AS MaxOfrights
SELECT t.user
FROM accTable t
GROUP BY t.user
PIVOT t.report;

Upvotes: 3

Related Questions