SQL Server how to join table in "rotated" format (returning columns instead of rows)?
Sorry for the chronic 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 the 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 result to 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 changes, so I cannot say with certainty how many columns I need. The request should be flexible.
Yes, it would be trivial to do it in code. But this query is one part of a complex 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 so as not to break the system. Any ideas?
This is MS SQL Server 2005, by the way.
Thanks.
a source to share
You can use the function PIVOT
. Here is a code snippet. This function needs columns in advance, but if you don't know the number of columns, you must do a dynamic SQL query. Take a look at this answer.
SELECT LAST_NAME, FIRST_NAME, [Male], [Female], [Alien], [Politician], [Teacher]
FROM
(SELECT LAST_NAME, FIRST_NAME, DEMO_GROUP
FROM Person) p
PIVOT
(
COUNT (DEMO_GROUP)
FOR DEMO_GROUP IN
( [Male], [Female], [Alien], [Politician], [Teacher] )
) AS pvt
ORDER BY LAST_NAME
a source to share
Assuming you know the Demo_Group list that will be returned in advance, you can do the following:
SELECT P.LAST_NAME, P.FIRST_NAME
, Sum( Case When Demo_Group = 'Male' Then 1 Else 0 End ) As Male
, Sum( Case When Demo_Group = 'Female' Then 1 Else 0 End ) As Female
, Sum( Case When Demo_Group = 'Teacher' Then 1 Else 0 End ) As Teacher
, Sum( Case When Demo_Group = 'Alien' Then 1 Else 0 End ) As Alien
FROM PERSON P
JOIN PERSON_DEMOGRAPHIC PD
ON PD.PERSON_ID = P.PERSON_ID
JOIN DEMOGRAPHIC D
ON D.DEMOGRAPHIC_ID = PD.DEMOGRAPHIC_ID
Group By P.LAST_NAME, P.FIRST_NAME
If the Demo_Group list of values is unknown, that is, you want the columns to be dynamically generated, then the only way to do this is to use some dynamic SQL. This is not something that SQL was designed and should instead be done in a middle tier or reporting tool.
a source to share