CalBob
asked on
Use the PIVOT statement without an aggregate
I am trying to write a query with the PIVOT function but can't figure it out. I have used PIVOT a number of times before but in this case I do not need to use any aggregate this time. Not sure if that is the problem. Here is a sample of my data:
Category User Rank
cat1 john 1
cat1 bill 2
cat1 lisa 3
cat2 lisa 1
cat2 bill 1
cat2 john 3
Each user only occurs once in each category and only has one rank for each category, but the ranks can be tied as shown in category 2 (lisa and bill are tied). However, I don't think that matters. I'm not trying to sum or get max values. I want my table to look like this:
User Cat1 Cat2
Bill 2 1
John 1 3
Lisa 3 1
I have more categories and users but I don't think that will change how it is done. It seems fairly simple for the PIVOT but apparently I don't understand it well enough to make it work.
I appreciate any help and suggestions.
Category User Rank
cat1 john 1
cat1 bill 2
cat1 lisa 3
cat2 lisa 1
cat2 bill 1
cat2 john 3
Each user only occurs once in each category and only has one rank for each category, but the ranks can be tied as shown in category 2 (lisa and bill are tied). However, I don't think that matters. I'm not trying to sum or get max values. I want my table to look like this:
User Cat1 Cat2
Bill 2 1
John 1 3
Lisa 3 1
I have more categories and users but I don't think that will change how it is done. It seems fairly simple for the PIVOT but apparently I don't understand it well enough to make it work.
I appreciate any help and suggestions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It seems to work great. Thanks. I don't know what you did that I didn't do (looks similar to what I tried) but I scrapped my attempts so I guess I'll never know. But yours works great.
Thanks for the grade. Good luck with your project. -Jim
I think you'll still need an aggregate. I tried replacing SUM(rank) with rank and it generated an error.