troubleshooting Question

Use the PIVOT statement without an aggregate

Avatar of CalBob
CalBob asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
4 Comments1 Solution635 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros