原始表格Test_Table
第一次查询:
SELECT Material,
(case when type='TA1' then num else 0 end) as TA1,
(case when type='TA2' then num else 0 end) as TA2,
(case when type='TA3' then num else 0 end) as TA3,
(case when type='TA4' then num else 0 end) as TA4,
(case when type='TB1' then num else 0 end) as TB1,
(case when type='TB2' then num else 0 end) as TB2
from test_table
第二次查询:
SELECT Material,
(case when type='TA1' then num else 0 end) as TA1,
(case when type='TA2' then num else 0 end) as TA2,
(case when type='TA3' then num else 0 end) as TA3,
(case when type='TA4' then num else 0 end) as TA4,
(case when type='TB1' then num else 0 end) as TB1,
(case when type='TB2' then num else 0 end) as TB2
into #t
from test_table
select Material, sum(TA1) as TA1, sum(TA2) as TA2,sum(TA3) as TA3,
sum(TA4) as TA4, sum(TB1) as TB1, sum(TB2) as TB2
from #t
group by Material