網路上找的 紀錄一下
報名資訊的資料表:
在資料表畫面中使用了年月區分了該時段的報名數，要將月份跟報名數以橫向的方式顯示
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
執行執行後的如下:
留言列表
雜七雜八 (1)
