SQL 的 PIVOT

2017-09-11

網路上找的紀錄一下

報名資訊的資料表:

在資料表畫面中使用了年月區分了該時段的報名數，要將月份跟報名數以橫向的方式顯示

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>;

從上方註解語法來看，第一部分 SELECT …. FROM 之間:

<non-pivoted column> : 不需要旋轉的欄位。
[first pivoted column] AS <column name> : 
第一個要旋轉的欄位 first pivoted column 的名稱將會對應於之後第三部分的 FOR column IN 裡面的的名稱。
第二部分 FROM ..... PIVOT 之間:
(<SELECT query that produces the data>) AS <alias for the source query> : 
這裡也就是你的資料來源的地方，要注意的是尾巴AS命名的名稱不可以漏掉一定要命名。
第三部分 PIVOT 跟 FOR:
<aggregation function>(<column being aggregated>) : 這裡是需要統計的欄位，例如我們的報名數。
[<column that contains the values that will become column headers>] : 將旋轉的欄位。
IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) : 這部分前面有提到，這邊的欄位名稱將對應第一部分的名稱。

照上面的格式編寫語法後，完成語法如下:

SELECT
Year As '年份',
[01] As '一月', [02] As '二月', [03] As '三月',
[04] As '四月', [05] As '五月', [06] As '六月',
[07] As '七月', [08] As '八月', [09] As '九月',
[10] As '十月', [11] As '十一月', [12] As '十二月'
FROM (
      SELECT Year, Month, SingupNumber FROM dbo.SingUpInfo
) As STable
PIVOT (
      SUM(SingupNumber) FOR
      Month IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])
) As PTable