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
