Help with PIVOT without aggregate function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with PIVOT without aggregate function

Author

Topic

simspace
Starting Member

2 Posts

Posted - 2008-07-01 : 13:50:20

I need to turn the following SQL results into a pivot table
I don't believe 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 14:04:38

[code]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[/code]

You could also cross tab this without using pivot.

[code]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 [/code]

simspace
Starting Member

2 Posts

Posted - 2008-07-01 : 14:24:57

visakh16 - YOU ROCK!!!!
Thanks very much.
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources