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
uj5u.com熱心網友回復：
一個簡單的條件聚合應該可以解決問題
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
uj5u.com熱心網友回復：
另一種使用 CTE 和聯接的方法。
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
| 程序 | a添加 | 一個子 | Amulti | 阿迪夫 | B添加 | bsub | bmulti | bdiv |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 0 | 1 | 1 | 2 | 2 | 0 | 1 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/483408.html
上一篇：在SQL中獲取XML檔案的所有值