Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

pivot? can't make it work. Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 4:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:31 AM
Points: 79, Visits: 430
Hi there,

I'm hoping for a pointer in the right direction as I can't suss this out & feel that it shouldn't be that complicated.

I have a table

id characteristic value(nvarhcar)
1 color blue
1 age 5
1 gender female
2 color green
2 cost 5
3 desc blahblah
3 lastcheck 27/jan/09
3 ...
3 ...

Client wants output as follows:

id characteristic1 value1 characteristic2 value2 ...
1 color blue age
2 color green desc blah

There is no aggregate.

Ideally query would by dynamic as new characteristics values may be added. not all id's have same number of characteristic values.

does this make sense?

any ideas / pointers gratefully accepted?

thanks.
Post #1465560
Posted Thursday, June 20, 2013 4:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 2,310, Visits: 2,790

PIVOT will give you the results for each ID in one single row:

if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
create table #temp (id int, characteristic varchar(15), value nvarchar(15))
insert into #temp
select 1, 'color', 'blue'
union all
select 1, 'age', '5'
union all
select 1, 'gender', 'male'
union all
select 2, 'color', 'green'
union all
select 2, 'cost', '10.0'
union all
select 2, 'description', 'testing'
union all
select 3, 'age', '7'
union all
select 3, 'gender', 'female'
union all
select 3, 'description', 'something'

select *
from #temp
pivot
(max(value)
for characteristic in ([color],[age],[gender],[cost],[description])
) as pvtTable

if OBJECT_ID('tempdb..#temp') is not null
drop table #temp

Expand the list in "for characteristic in ([color],[age],[gender],[cost],[description])" with all possible options. In your sample data it is not completly clear what all values are, so that's up to you. When there is no row present for a specific characteristic a NULL value will be shown in the resultset.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1465570
Posted Thursday, June 20, 2013 5:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:31 AM
Points: 79, Visits: 430
Thanks, it seems like a complicated solution to what I thought would be simpler.

I'll have to make it dynamic because the 'characteristic' values may change, but, presuming there's no easier way, thanks for the taking the time to reply.

Post #1465592
Posted Thursday, June 20, 2013 5:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 2,310, Visits: 2,790
Take a look at the blog from Aaron Bertrand where he builds a dynamic PIVOT solution:
http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1465596
Posted Thursday, June 20, 2013 5:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 2,658, Visits: 4,733
Do you find this simpler?
SELECT	id,
MAX( CASE WHEN characteristic = 'color' THEN value ELSE NULL END ) AS color,
MAX( CASE WHEN characteristic = 'age' THEN value ELSE NULL END ) AS age,
MAX( CASE WHEN characteristic = 'gender' THEN value ELSE NULL END ) AS gender,
MAX( CASE WHEN characteristic = 'cost' THEN value ELSE NULL END ) AS cost,
MAX( CASE WHEN characteristic = 'description' THEN value ELSE NULL END ) AS description
FROM #temp AS t
GROUP BY id

If Yes, these are called CROSS TABS and you can find more information on this technique below
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1465600
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse