How to do sql pivot with multiple values in single column

Question

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

Gordon Linoff · Accepted Answer · 2019-05-12 11:25:35Z

2

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;

answered May 12, 2019 at 11:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to do sql pivot with multiple values in single column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related