SQL Server, how to join a table in a "rotated" format (returning columns instead of rows)?

Question

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.

Answer 1

Assuming the list of Demo_Group to be returned is known ahead of time, 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 list of Demo_Group values is not known, meaning you want the columns to be dynamically generated, then the only way to do that is using some fugly dynamic SQL. This is not what SQL was designed to do and instead should be done in a middle-tier or reporting tool.

Answer 2

You can use the PIVOT function. Here´s a piece of code. This function needs the columns in advance, but if you do not know the number of columns, you should make 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