pivot? can't make it work.

Question

Post reply

pivot? can't make it work.

snomadj

SSCarpal Tunnel

Points: 4491
More actions
June 20, 2013 at 4:05 am

#395005

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

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 (,[age],[gender],[cost],[description])

) as pvtTable

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

drop table #temp

Expand the list in "for characteristic in (,[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’! **

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 2

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.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

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’! **

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Do you find this simpler?

SELECTid,

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

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs [/url]

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/