MS SQL Server Dynamic 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);
-- ===============================================
-- 名稱：[DynamicPIVOT]
-- 說明：動態PIVOT
-- 參數：@SQLCmd
NVARCHAR(MAX), @FixField NVARCHAR(MAX), @SummaryType NVARCHAR(MAX),
@SummaryColumn NVARCHAR(MAX), @PivotColumn NVARCHAR(MAX)
-- 擲回：TABLE
-- 範例：EXEC DynamicPIVOT
'SELECT [ITEM], [Year], [Month], (CAST([Year] AS VARCHAR) + SUBSTRING(''0'' +
CAST([Month] AS VARCHAR), LEN(CAST([Month] AS VARCHAR)), 2)) AS [YM], [QTY] FROM [ORDERS]', '[ITEM],
[Year]', 'SUM', '[QTY]', '[Month]'
-- ===============================================
CREATE PROCEDURE
DynamicPIVOT @SQLCmd
NVARCHAR(MAX), @RowArea NVARCHAR(MAX), @SummaryType NVARCHAR(MAX), @DataArea NVARCHAR(MAX), @ColumnArea NVARCHAR(MAX)
AS
BEGIN
-- Dev Test
-- DECLARE @SQLCmd NVARCHAR(MAX), @RowArea NVARCHAR(MAX),
@SummaryType NVARCHAR(MAX), @DataArea NVARCHAR(MAX), @ColumnArea NVARCHAR(MAX)
-- SET @SQLCmd = 'SELECT [ITEM], [Year], [Month],
(CAST([Year] AS VARCHAR) + SUBSTRING(''0'' + CAST([Month] AS VARCHAR),
LEN(CAST([Month] AS VARCHAR)), 2)) AS
[YM], [QTY] FROM [ORDERS]'
-- SET @RowArea = '[ITEM]'
-- SET @SummaryType = 'SUM'
-- SET @DataArea = '[QTY]'
-- SET @ColumnArea = '[YM]'
-- 執行SQL String
DECLARE @RunSQL
NVARCHAR(MAX)
-- Pivot Columns
DECLARE @PivotColumns
NVARCHAR(MAX)
-- GET Pivot Column
SET @RunSQL
=
N'SET @COLUMN = (
SELECT
''['' +
CONVERT(NVARCHAR, ' + @ColumnArea + ') + ''],''
FROM
('+ @SQLCmd
+') C
GROUP BY
' + @ColumnArea
+ '
ORDER BY
1
FOR XML PATH('''')
)'
EXECUTE SP_EXECUTESQL @RunSQL,
N'@COLUMN
VARCHAR(MAX) OUTPUT',
@COLUMN = @PivotColumns OUTPUT
SET @PivotColumns
= SUBSTRING(@PivotColumns, 0, LEN(@PivotColumns))
-- Read Pivot
SET @RunSQL
=
N'
SELECT
*
FROM
(
SELECT ' + @RowArea
+ ',' + @ColumnArea + ',' + @DataArea + ' FROM (' + @SQLCmd + ') T
) F
PIVOT
(
' + @SummaryType
+ ' (' + @DataArea + ')
FOR ' + @ColumnArea
+ ' IN (' + @PivotColumns + ')
) P
'
SELECT @RunSQL
EXECUTE SP_EXECUTESQL @RunSQL
END
留言
張貼留言