查询，以获得所需格式的输出

Question

Proc  type  add sub multi div
1       A     1   0    1   1
1       B     2   2    0   1

输出格式为

Proc Aadd Asub Amulti Adiv Badd Bsub Bmulti Bdiv
1     1    0     1      1    2    2   0      1

Answer 1

一个简单的条件聚合就可以达到这个目的

Select Proc
,Aadd  = max( case when type='A' then add   else 0 end)
,Asub  = max( case when type='A' then sub   else 0 end)
,Amuti = max( case when type='A' then multi else 0 end)
,Adiv  = max( case when type='A' then div   else 0 end)
,Badd  = max( case when type='B' then add   else 0 end)
,Bsub  = max( case when type='B' then sub   else 0 end)
,Bmuti = max( case when type='B' then multi else 0 end)
,Bdiv  = max( case when type='B' then div   else 0 end)
From  YourTable
Group By Proc

Answer 2

另一种使用CTE和join的方法。

declare @table table(Proce  int, type  char(1), addi int, sub int, multi int, div int)
insert into @table values
(1,'A',     1,   0,    1,   1),
(1,'B',     2,   2,    0,   1);
;with cte_a as
(
SELECT proce, max(addi) as Aadd, max(sub) as Asub, max(multi) as Amulti, max(div) as Adiv  
FROM @table where type = 'A'
group by proce
),cte_b as
(
SELECT proce, max(addi) as Badd, max(sub) as Bsub, max(multi) as Bmulti, max(div) as Bdiv  
FROM @table where type = 'B'
group by proce
)
SELECT a.proce,a.aAdd, a.aSub, a.Amulti, a.Adiv,b.BAdd,b.bsub, b.bmulti, b.bdiv
from cte_a as a
join cte_b as b
on a.Proce = b.Proce

<表类>过程aAddaSubAmultiAdivBAddbsubbmultibdivtbody><<tr>110112201