How to use Pivot on two columns in sql

Question

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

Answer 1

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