Pivot table with multiple column in sql?

Question

i want to pivot my table with multiple column

bdate               th_name     amt     NetSale     GST         Occupancy
3/18/18 12:00 AM    Screen 1    212540  169474.1128 43065.88718 1752
3/18/18 12:00 AM    Screen 2    242585  193562.5    49022.5     2065
3/18/18 12:00 AM    Screen 3    45840   36759.26906 9080.730929 438
3/18/18 12:00 AM    Screen 4    43470   34855.73358 8614.266417 424
3/18/18 12:00 AM    Screen 5    33600   26250       7350        122
3/19/18 12:00 AM    Screen 1    1755    1376.059322 378.940677  13
3/19/18 12:00 AM    Screen 2    1780    1490.598516 289.401481  20
3/19/18 12:00 AM    Screen 3    365     309.322032  55.677966   5
3/19/18 12:00 AM    Screen 4    1110    940.677965  169.322032  14

i want result like below

bdate              screen1amt screen1netsale screen1GSt    screen1Occupancy screen2amt screen2netsale screen3GSt screen3Occupancy screen4amt screen4netsale screen4GSt screen4Occupancy screen5amt screen5netsale screen5GSt screen5Occupancy
3/18/18 12:00 AM   212540     169474.1128    43065.88718   1752...........like screen2,screen3,screen4,screen5
3/19/18 12:00 AM   1755       1376.059322    378.940677    13.............like screen2,screen3,screen4,screen5

score 4 · Answer 1 · answered Apr 06 '18 at 11:40

I think the simplest way is conditional aggregation:

select bdate,
       max(case when th_name = 'Screen 1' then amt end) as screen1_amt,
       max(case when th_name = 'Screen 1' then netsale end) as screen1_netsale,
       max(case when th_name = 'Screen 1' then gst end) as screen1_gst,
       max(case when th_name = 'Screen 1' then occupancy end) as screen1_occupancy,
       max(case when th_name = 'Screen 2' then amt end) as screen2_amt,
       max(case when th_name = 'Screen 2' then netsale end) as screen2_netsale,
       max(case when th_name = 'Screen 2' then gst end) as screen2_gst,
       max(case when th_name = 'Screen 2' then occupancy end) as screen2_occupancy,
       . . .
from t
group by bdate;

score 4 · Accepted Answer · answered Apr 06 '18 at 12:00

Or you can PIVOT (assuming you don't need to go dynamic)

Example

Select *
 From  (
        Select bdate
              ,B.*
         From  YourTable A
         Cross Apply ( values (replace(th_name,' ','')+'amt',amt)
                             ,(replace(th_name,' ','')+'NetSale',NetSale)
                             ,(replace(th_name,' ','')+'GST',GST)
                             ,(replace(th_name,' ','')+'Occupancy',Occupancy)
                     ) B(Item,Value)
       ) src
 Pivot (max(Value) for Item in ([Screen1amt],[Screen1NetSale],[Screen1GST],[Screen1Occupancy],[Screen2amt],[Screen2NetSale],[Screen2GST],[Screen2Occupancy],[Screen3amt],[Screen3NetSale],[Screen3GST],[Screen3Occupancy],[Screen4amt],[Screen4NetSale],[Screen4GST],[Screen4Occupancy]) ) pvt

Returns

Pivot table with multiple column in sql?

2 Answers2