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
>Solution :
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