SQL Server, how to join a table in a "rotated" format (returning columns instead of rows)?
- by Joshua Carmody
Sorry for the lame title, my descriptive skills are poor today.
In a nutshell, I have a query similar to the following:
SELECT P.LAST_NAME, P.FIRST_NAME, D.DEMO_GROUP
FROM PERSON P
JOIN PERSON_DEMOGRAPHIC PD ON PD.PERSON_ID = P.PERSON_ID
JOIN DEMOGRAPHIC D ON D.DEMOGRAPHIC_ID = PD.DEMOGRAPHIC_ID
This returns output like this:
LAST_NAME FIRST_NAME DEMO_GROUP
---------------------------------------------
Johnson Bob Male
Smith Jane Female
Smith Jane Teacher
Beeblebrox Zaphod Male
Beeblebrox Zaphod Alien
Beeblebrox Zaphid Politician
I would prefer the output be similar to the following:
LAST_NAME FIRST_NAME Male Female Teacher Alien Politician
---------------------------------------------------------------------------------------------------------
Johnson Bob 1 0 0 0 0
Smith Jane 0 1 1 0 0
Beeblebrox Zaphod 1 0 0 1 1
The number of rows in the DEMOGRAPHIC table varies, so I can't say with certainty how many columns I need. The query needs to be flexible.
Yes, it would be trivial to do this in code. But this query is one piece of a complicated set of stored procedures, views, and reporting services, many of which are outside my sphere of influence. I need to produce this output inside the database to avoid breaking the system. Any ideas?
This is MS SQL Server 2005, by the way.
Thanks.
