使用SQL SERVER PIVOT实现行列转置

一般我们在使用SQL语句实现行列转置时候，最常用的方法无外乎就是 case语句来实现，但是如果需要需要转置的列太多，那么case起来语句就无限庞大，十分不方便，sql server中的PIVOT就可以帮助我们解决此类问题

PIVOT語法，如下：

SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name>FROM (<SELECT query that produces the data>) AS <alias for the source query>PIVOT( <aggregation function>(<column being aggregated>)FOR[<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column])) AS <alias for the pivot table><optional ORDER BY clause>;

PIVOT語法剖析：

PIVOT的語法分三層，用三個步驟來使用。
第一步驟：先把要PIVOT的原始資料查詢(Query)好。
第二步驟：設定好PIVOT的欄位與方式。
第三步驟：依PIVOT好了的資料，呈現結果。

SELECT <non-pivoted column>, ---- 第三步驟在此，呈現PIVOT後的資料。 [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name>FROM (<SELECT query that produces the data>) ---- 第一步驟在此，準備資料(Query)。 AS <alias for the source query>PIVOT ---- 第二步驟在此,依第一步驟的資料欄位來設定PIVOT方式。( <aggregation function>(<column being aggregated>)FOR[<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column])) AS <alias for the pivot table><optional ORDER BY clause>;

用實戰案例說明：

實戰案例一：

--## 一維PIVOT
目的：統計各狀態(ldap_sts)的數量。
select * ---- 第三步：把PIVOT好的資料直接呈現出來。
from
(
    select [ldap_id], [ldap_sts] from ccldap   -- 第一步：準備資料。
-- 只從原資料檔選了兩個欄位,PK欄位(ldap_id)與狀態欄位(ldap_sts)。
) S -- 一定要有，不然會語法錯誤。
pivot
(
    count([ldap_id]) -- 統計計數數量
    for [ldap_sts] in ([1],[2],[3],[4],[5],[6],[7]) -- 為欄位[ldap_sts]的狀態值[1][2]…[7]進行統計計算。
-- 注意：[1][2]…[7]是[ldap_sts]的值，以欄位表示法來描述[ldap_sts]的值。
) P -- 一定要有，不然會語法錯誤。

下面是執行結果：

1 2 3 4 5 6 7 <---狀態值
--------- --------- --------- --------- --------- --------- ---------
1 12528 68519 120 8 5 36 <---狀態數量

(1 個資料列受到影響)

======================================================
# 實戰案例二：

--## 二維PIVOT
目的：統計不同用途(app_rsn_cod )下，各狀態(ldap_sts)的數量。
select * -- 第三步：把PIVOT好的資料直接呈現出來。
from
(
    select [ldap_id], [ldap_sts], [app_rsn_cod] from ccldap -- 第一步：準備資料。
         -- 從原資料檔選了三個欄位,PK欄位(ldap_id)、狀態欄位(ldap_sts)與用途欄位(app_rsn_cod)。
) S -- 一定要有，不然會語法錯誤。
pivot
(
    count([ldap_id])-- 統計計數數量
    for [ldap_sts] in ( [1],[2],[3],[4],[5],[6],[7]) -- 為欄位[ldap_sts]的狀態值[1][2]…[7]進行統計計算。
-- 注意：[1][2]…[7]是[ldap_sts]的值，以欄位表示法來描述[ldap_sts]的值。
) P

下面是執行結果：

(用途)      (狀態1)   (狀態2)   (狀態3)   (狀態4)   (狀態5)   (狀態6)   (狀態7)
app_rsn_cod 1         2         3         4         5         6         7
----------- --------- --------- --------- --------- --------- --------- ---------
NULL        0         12515     59676     0         2         0         0
1           1         10        8104      1         4         5         0
2           0         3         739       119       2         0         36

(3 個資料列受到影響)

注意到了嗎，在此例的第二步驟，並未設定用途欄位(app_rsn_cod)，但在最後的PIVOT結果資料卻神奇的合併(join)成希望達到的效果。

## 補充一個應用 on 2013/1/7：

-- 財產折舊傳票 Query Command.
DECLARE @ac_yr NCHAR(4);
SET @ac_yr = '2012';
SELECT [ac_yr_tw]=Cast(ac_yr - 1911 AS VARCHAR(3))
,[tckt_ym]=adg.cvtY4mToY3m(tckt_ym)
,[財產預算內],[財產預算外],[實小財產預算內],[軟體預算內],[軟體預算外],[大修預算內],[大修預算外]
FROM (
SELECT ac_yr -- key
,tckt_ym -- key
,tckt_num -- measure
,[pivot_col]=Case
When bdg_tpe = 'A' and dpr_grp = 'A' Then '財產預算內' -- pivot column
When bdg_tpe = 'B' and dpr_grp = 'A' Then '財產預算外'
When bdg_tpe = 'A' and dpr_grp = 'B' Then '軟體預算內'
When bdg_tpe = 'B' and dpr_grp = 'B' Then '軟體預算外'
When bdg_tpe = 'A' and dpr_grp = 'D' Then '大修預算內'
When bdg_tpe = 'B' and dpr_grp = 'D' Then '大修預算外'
End
FROM gcastdprtckt
WHERE ac_yr = @ac_yr
) S
PIVOT
(
MAX(tckt_num) -- 其實只會有一筆，但不用aggregation function 不會出現。
FOR [pivot_col] in ([財產預算內],[財產預算外],[實小財產預算內],[軟體預算內],[軟體預算外],[大修預算內],[大修預算外])
) P

# 結果象這樣

原文转载：http://rely1020.blog.ithome.com.tw/post/1606/39111

使用SQL SERVER PIVOT实现行列转置

相关推荐