MS SQL Server PIVOT 教學

-- 建立訂單

CREATE TABLE [ORDERS]([Item] NVARCHAR(MAX), [Store] NVARCHAR(MAX), [Year] INT, [Month] INT, [Day] INT, [QTY] INT, [Price] INT)

INSERT INTO [ORDERS] VALUES(N'SQL Server資料採礦與商業智慧-適用SQL Server 2014/2012', N'博客來', 2014, 1, 10, 1, 532);

INSERT INTO [ORDERS] VALUES(N'SQL Server資料採礦與商業智慧-適用SQL Server 2014/2012', N'天瓏', 2014, 1, 5, 2, 512);

INSERT INTO [ORDERS] VALUES(N'SQL Server資料採礦與商業智慧-適用SQL Server 2014/2012', N'誠品', 2014, 2, 1, 1, 560);

INSERT INTO [ORDERS] VALUES(N'SQL Server資料採礦與商業智慧-適用SQL Server 2014/2012', N'三民', 2014, 3, 6, 10, 502);

INSERT INTO [ORDERS] VALUES(N'JavaFX 8技術手冊', N'博客來', 2014, 3, 10, 1, 585);

INSERT INTO [ORDERS] VALUES(N'JavaFX 8技術手冊', N'天瓏', 2013, 10, 5, 2, 555);

INSERT INTO [ORDERS] VALUES(N'JavaFX 8技術手冊', N'誠品', 2013, 11, 1, 1, 650);

INSERT INTO [ORDERS] VALUES(N'JavaFX 8技術手冊', N'金石堂', 2014, 4, 6, 1, 555);

INSERT INTO [ORDERS] VALUES(N'嵌入式 Android 系統', N'博客來', 2013, 3, 10, 2, 632);

INSERT INTO [ORDERS] VALUES(N'嵌入式 Android 系統', N'金石堂', 2014, 4, 6, 1, 630);

INSERT INTO [ORDERS] VALUES(N'嵌入式 Android 系統', N'三民', 2014, 2, 6, 1, 598);

-- 顯示每月的銷售數量

SELECT

     *

FROM

     (SELECT [Item],  [Year], [Month],  [QTY]

      FROM [ORDERS]

      GROUP BY [Item],  [Year], [Month],  [QTY]) P

PIVOT(

     SUM(QTY)

     FOR [Month] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])

) AS PVT

ORDER BY [Item], [Year]

-- 顯示每月的銷售額

SELECT

     *

FROM

     (SELECT [Item],  [Year], [Month],  ([QTY] * [Price])  AS [Price]

      FROM [ORDERS]

      GROUP BY [Item],  [Year], [Month], [QTY], [Price]) P

PIVOT(

     SUM([Price])

     FOR [Month] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])

) AS PVT

ORDER BY [Item], [Year]
.


動態產生Pivot欄位

DECLARE @COLUMNS VARCHAR(MAX)

DECLARE @SQLCMD NVARCHAR(MAX)

SET @COLUMNS = (SELECT ',[' + CAST([MONTH] AS VARCHAR) + ']'

                 FROM [ORDERS]

                 GROUP BY [MONTH]

                 ORDER BY 1

                 FOR XML PATH(''))

SET @COLUMNS = SUBSTRING(@COLUMNS, 2, LEN(@COLUMNS))

SET @SQLCMD = N'

SELECT

     *

FROM

     (SELECT [Item],  [Year], [Month],  [QTY]

      FROM [ORDERS]

      GROUP BY [Item],  [Year], [Month],  [QTY]) P

PIVOT(

     SUM(QTY)

     FOR [Month] IN (' + @COLUMNS + ')

) AS PVT

ORDER BY [Item], [Year]

'

EXECUTE sp_executesql @SQLCMD