问题描述:

I have data like below in my table. Flag is bit and label is varchar.

parentid code label flag

1 abc hello false

1 xyz bye false

1 qrt hi true

I need to fetch the records as

parentid label_abc flag_abc label_xyz flag_xyz label_qrt flag_qrt

I can only fetch only label right now using Pivot, but when i give second aggregate function for flag it gives error (Incorrect syntax near ','.). Is there any way to fetch two columns using Pivot.

I did something like this:

SELECT distinct

parentid

, [abc] as label_abc

, [xyz] as label_xyz

, [qrt] as label_qrt

FROM (

Select

parentid,

label,code

FROM items

) a

Pivot (

Max(label), max (flag)

FOR code in ([abc], [xyz], [qrt]

) as Pvt

网友答案:

I find it a bit tricky to do this using the pivot operator, and a lot easier to use conditional aggregation instead:

select 
    parentid, 
    max(case when code = 'abc' then label end) as label_abc, 
    max(case when code = 'abc' then flag  end) as flag_abc, 
    max(case when code = 'xyz' then label end) as label_xyz, 
    max(case when code = 'xyz' then flag  end) as flag_xyz, 
    max(case when code = 'qrt' then label end) as label_qrt, 
    max(case when code = 'qrt' then flag  end) as flag_qrt
from ( 
    select parentid, code, label, cast(flag as int) flag 
    from items 
) src
group by parentid;

Sample SQL Fiddle

网友答案:

yes, but they need to be pivoted separately. it takes a little bit of fancy footwork, but it should look more like:

select distinct parentid
, [abc1] as label_abc 
, [xyz1] as label_xyz 
, [qrt1] as label_qrt
, [abc2] as flag_abc 
, [xyz2] as flag_xyz 
, [qrt2] as flag_qrt
from (
    select parentid
    , label
    , label + '1' as code1
    , label + '2' as code2
    from items 
) as a
pivot (
    max(label) for code1 in ([abc1], [xyz1], [qrt1])
) as pvt1
pivot (
    max(flag) for code2 in ([abc2], [xyz2], [qrt2])
) as pvt2
相关阅读:
Top