《转》sql server行转列函数的理解
參考資料：使用 PIVOT 和 UNPIVOT http://technet.microsoft.com/zh-tw/library/ms177410.aspx
前言：
T-SQL 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)成希望達到的效果。
