SQL Server How do I convert those columns to rows?

c86crjj0  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(140)

after convert, it should be

month   1 2    3 4 5 6 7 8 9 10 11 12   
planqty 0 0 6230 0 0 0 0 0 0  0  0  0    
actqty  0 0 2631 0 0 0 0 0 0  0  0  0

I will be so appreciated if you can help.

wfveoks0

wfveoks01#

You want APPLY (i.e. CROSS APPLY ) in order to convert columns to row wise.

select 
       qtynames as Month,
       max(case when dt = '01' then qty end) [01],
       max(case when dt = '02' then qty end) [02], 
       max(case when dt = '03' then qty end) [03],...
from table t cross apply (
    values ('month', dt, 'planqty', planqty), ('month', dt, 'actqty', actqty)
)a(mnames, dates, qtynames, qty)
group by qtynames

相关问题