How to transpose columns?-CodePudding

I have this table

SKU	CITY	MOV	TYPE	UNI	IMP
1	116	49	Caducidad	3	203.889
1	116	48	Daño	3	203.889
1	116	47	Robo	NULL	NULL

And I'm trying to transpose 'Type' column to display something like this

SKU	CITY	TYPE_UNI_CADUCIDAD	TYPE_IMP_CADUCIDAD	TYPE_UNI_DAÑO	TYPE_IMP_DAÑO	TYPE_UNI_ROBO	TYPE_IMP_ROBO
1	116	3	203.889	3	203.889	NULL	NULL

I tried case and pivot but not really working

SELECT SKU, CITY, 
case 
when MOV=49 then sum(Total_Imp) end as Type_Imp_Caducidad ,
case
when MOV=48 then sum(Total_Imp) end as Type_Imp_Daño ,
case
when MOV=47 then sum(Total_Imp) end as Type_Imp_Robo
from #movimientos 
where Id_Num_SKU=11466978
group by SKY, CITY, MOV

CodePudding user response：

As both comentsalready told you you need to aggreate the hole CASE WHEN to have an aggregation function

SELECT SKU, CITY, 
sum(case 
when MOV=49 then Total_Imp end) as Type_Imp_Caducidad ,
sum(case
when MOV=48 then Total_Imp end) as Type_Imp_Daño ,
SUM(case
when MOV=47 then Total_Imp end) as Type_Imp_Robo
from #movimientos 
where Id_Num_SKU=11466978
group by SKY, CITY

CodePudding user response：

You need to remove MOV from the GROUP BY and then aggregate the whole CASE expression

SELECT
  m.SKU,
  m.CITY, 
  SUM(case when m.MOV = 49 then m.Total_Uni end) as Type_Uni_Caducidad,
  SUM(case when m.MOV = 49 then m.Total_Imp end) as Type_Imp_Caducidad,
  SUM(case when m.MOV = 48 then m.Total_Uni end) as Type_Uni_Daño,
  SUM(case when m.MOV = 48 then m.Total_Imp end) as Type_Imp_Daño,
  SUM(case when m.MOV = 47 then m.Total_Uni end) as Type_Uni_Robo,
  SUM(case when m.MOV = 47 then m.Total_Imp end) as Type_Imp_Robo
from #movimientos m
where m.Id_Num_SKU = 11466978
group by
  m.SKY,
  m.CITY;