This is my sample Table, i want to pivot the session column and get the Qty and total in single row

EmpName              Session         Qty        Amount
--------------------------------------------------------
Arindam               Dinner           3    195.00
Arindam               Lunch            3    180.00
Arindam               HI Tea          10    80.00
Arindam               Snacks          10    370.00

i need the the output in the below format

EmpName  Dinner DinnerAmt Lunch LunchAmt  HITea  HITeaAmt Snacks  SnacksAmt 
------------------------------------------------------------------------------
Arindam     3    195.00     3    180.00     10   80.00      10    370.00

This is the query.

select M_ccsv_Employee.vcName as EmpName,M_CAN_Session.vcName as SessionName,
sum(T_CanteenSub.iQty ) as Qty,sum( T_CanteenSub.dTotal ) as Total 
from T_Canteen inner join M_ccsv_Employee 
on T_Canteen.iEmpKey = M_ccsv_Employee.iKey  
left join M_CAN_Session 
on M_CAN_Session.iKey = T_Canteen.iSessionKey 
inner join T_CanteenSub 
on T_CanteenSub.iTransKey = T_Canteen.iKey 
where ((right(CONVERT(date,T_Canteen.vcBillDate, 103),10)) between
(right(CONVERT(date,'01/11/2018', 103),10)) and
(right(CONVERT(date,'11/05/2019', 103),10))) and 
M_ccsv_Employee.vcName = 'Arindam' 
group by M_ccsv_Employee.vcName,M_CAN_Session.vcName

1 Answers

2
Gordon Linoff On

You can use conditional aggregation:

with t as (
      <your query here>
     )
select empname,
       max(case when session = 'Dinner' then qty end) as dinner_qty,
       max(case when session = 'Dinner' then amount end) as dinner_amount,
       max(case when session = 'Lunch' then qty end) as lunch_qty,
       max(case when session = 'Lunch' then amount end) as lunch_amount,
       max(case when session = 'HI Tea' then qty end) as hitea_qty,
       max(case when session = 'HI Tea' then amount end) as hitea_amount,
       max(case when session = 'Snacks' then qty end) as snacks_qty,
       max(case when session = 'Snacks' then amount end) as snacks_amount
from t
group by empname;