How do I pivot a sql server table

Question

I was wondering if I can turn this

id     part_id,   type,    value
1      1         'type1'  'aaaa'
2      1         'type2'  'bbbb'
3      1         'type3'  'cccc'
4      2         'type1'  'dddd'
5      2         'type2'  'eeee'
6      2         'type3'  'ffff'

into this

part_id,   type1value,   type2value,   type3value
1          aaaa          bbbb          cccc
2          dddd          eeee          ffff

And use nothing but TSQL

I think "pivot" command can do the job but I couldn't possibly seems to make it work...

I need help!

score -1 · Answer 1 · answered Jul 13 '14 at 15:58

Here is the correct format for the PIVOT that you need:

set nocount on;

with data as ( select * from ( values
        ( 1,  1,  'type1',  'aaaa')
       ,( 2,  1,  'type2',  'bbbb')
       ,( 3,  1,  'type3',  'cccc')
       ,( 4,  2,  'type1',  'dddd')
       ,( 5,  2,  'type2',  'eeee')
       ,( 6,  2,  'type3',  'ffff')
    )data(id,part_id,type,value)
)
select
     part_id
    ,max(coalesce(type1,' ')) as type1
    ,max(coalesce(type2,' ')) as type2
    ,max(coalesce(type3,' ')) as type3
from data
pivot (max(value) for type in (type1, type2, type3) ) pvt
group by
    part_id
;

which nicely returns the following as required:

part_id     type1 type2 type3
----------- ----- ----- -----
1           aaaa  bbbb  cccc
2           dddd  eeee  ffff

How do I pivot a sql server table

1 Answers1