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

Output should be in the format

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

CodePudding user response：

A simple conditional aggregation should do the trick

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

CodePudding user response：

Another approach using CTE and Joins.

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