Help with PIVOT without aggregate function

[simspace]

I need to turn the following SQL results into a pivot table
I don't beleieve an aggregate function will help with this one.
The query is simplified for the purpose of this post.

I have used the SQL 2005 PIVOT operator to show columns of aggregated data.
However, I'm not sure how to turn the following SQL results into a pivot table.
Any help/suggestions would be appreciated.

Thanks!
Chris
============
The SQL:
============

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

============
The 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

============
What I need:
============

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

 

[simspace]

Well, I got the answer from a SQL forum. Here it is...

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

You could also cross tab this without using pivot.

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