SQL Server 2005 PIVOT Operator

For my current project I needed to convert rows of data to columns. I learned how to do this with the SQL Server 2005 PIVOT Operator.

Here's the original code...

SELECT p.user_id, pit.data_name, pi.item_value
FROM profile p
JOIN profile_item pi ON pi.profile_id = p.profile_id
JOIN profile_item_type pit ON pit.profile_item_type_id = pi.profile_item_type_id
AND pit.data_name in ('age','gender','marital_status','family_size','household_income','ethnicity')
WHERE p.user_id = 100

...and the original results...

p.user_id  pit.data_name        pi.item_value
---------  -------------        -------------
100        age                  Under 18
100        gender               Male
100        marital_status       Single
100        family_size          1
100        household_income     Under $15,000
100        ethnicity            Rather not disclose

...and now the new SQL using the PIVOT operator...

SELECT *
FROM (SELECT p.user_id, pit.data_name, pi.item_value
          FROM profile p
          JOIN profile_item pi ON pi.profile_id = p.profile_id
          JOIN profile_item_type pit ON pit.profile_item_type_id = pi.profile_item_type_id
          AND pit.data_name in ('age','gender','marital_status','family_size','household_income','ethnicity')
WHERE p.user_id = 100)m
PIVOT (MAX(item_value) FOR data_name IN ([age],[gender],[marital_status],[family_size],[household_income],[ethnicity]))p

...and the desired results...

p.user_id  age        gender    marital_status   family_size  household_income   ethnicity
---------  ---        ------    --------------   -----------  ----------------   ---------
100        Under 18   Male      Single           1            Under $15,000      Rather not disclose

You could also cross tab this without using pivot operator...

SELECT p.user_id,
MAX(CASE WHEN pit.data_name='age' THEN pi.item_value ELSE NULL END) AS age,
MAX(CASE WHEN pit.data_name='gender' THEN pi.item_value ELSE NULL END) AS gender,
MAX(CASE WHEN pit.data_name='marital_status' THEN pi.item_value ELSE NULL END) AS marital_status,
MAX(CASE WHEN pit.data_name='family_size' THEN pi.item_value ELSE NULL END) AS family_size,
MAX(CASE WHEN pit.data_name='household_income' THEN pi.item_value ELSE NULL END) AS household_income,
MAX(CASE WHEN pit.data_name='ethnicity' THEN pi.item_value ELSE NULL END) AS ethnicity
FROM profile p
JOIN profile_item pi ON pi.profile_id = p.profile_id
JOIN profile_item_type pit ON pit.profile_item_type_id = pi.profile_item_type_id
AND pit.data_name in ('age','gender','marital_status','family_size','household_income','ethnicity')
WHERE p.user_id = 100
GROUP BY p.user_id