动态Pivot(1)

USE master;
GO

IF OBJECT_ID('dbo.sp_pivot') IS NOT NULL
DROP PROC dbo.sp_pivot;
GO

CREATE PROC dbo.sp_pivot
@query    AS NVARCHAR(MAX),
@on_rows  AS NVARCHAR(MAX),
@on_cols  AS NVARCHAR(MAX),
@agg_func AS NVARCHAR(MAX) = N'MAX',
@agg_col  AS NVARCHAR(MAX)
AS

DECLARE
@sql     AS NVARCHAR(MAX),
@cols    AS NVARCHAR(MAX),
@newline AS NVARCHAR(2);

SET @newline = NCHAR(13) + NCHAR(10);

-- 如果输入是有效的表或视图，则使用它构造SELECT语句
-- 
IF COALESCE(OBJECT_ID(@query, N'U'),
OBJECT_ID(@query, N'V')) IS NOT NULL
SET @query = N'SELECT * FROM ' + @query;

-- 把该查询用作派生表
SET @query = N'(' + @query + @newline + N'       ) AS Query';

-- 处理@agg_col中的*
IF @agg_col = N'*'
SET @agg_col = N'1';

-- 构造列的列表
SET @sql =
N'SET @result = '                                    + @newline +
N'  STUFF('                                          + @newline +
N'    (SELECT N'','' + '
+ N'QUOTENAME(pivot_col) AS [text()]'       + @newline +
N'     FROM (SELECT DISTINCT('
+ @on_cols + N') AS pivot_col'              + @newline +
N'           FROM' + @query + N') AS DistinctCols'   + @newline +
N'     ORDER BY pivot_col'                           + @newline +
N'     FOR XML PATH('''')),'                         + @newline +
N'    1, 1, N'''');'

EXEC sp_executesql
@stmt   = @sql,
@params = N'@result AS NVARCHAR(MAX) OUTPUT',
@result = @cols OUTPUT; 
-- 创建 PIVOT 查询
SET @sql =
N'SELECT *'                                           + @newline +
N'FROM'                                               + @newline +
N'  ( SELECT '                                        + @newline +
N'      ' + @on_rows + N','                           + @newline +
N'      ' + @on_cols + N' AS pivot_col,'              + @newline +
N'      ' + @agg_col + N' AS agg_col'                 + @newline +
N'    FROM '                                          + @newline +
N'      ' + @query                                    + @newline +
N'  ) AS PivotInput'                                  + @newline +
N'  PIVOT'                                            + @newline +
N'    ( ' + @agg_func + N'(agg_col)'                  + @newline +
N'      FOR pivot_col'                                + @newline +
N'        IN(' + @cols + N')'                         + @newline +
N'    ) AS PivotOutput;'

EXEC sp_executesql @sql;
GO

EXEC Northwind.dbo.sp_pivot
@query    = N'dbo.Orders',
@on_rows  = N'EmployeeID AS empid, YEAR(OrderDate) AS order_year',
@on_cols  = N'MONTH(OrderDate)',
@agg_func = N'COUNT',
@agg_col  = N'*';

EXEC Northwind.dbo.sp_pivot
@query    = N'
SELECT O.OrderID, EmployeeID, OrderDate, Quantity, UnitPrice
FROM dbo.Orders AS O
JOIN dbo.[Order Details] AS OD
ON OD.OrderID = O.OrderID',
@on_rows  = N'EmployeeID AS empid',
@on_cols  = N'YEAR(OrderDate)',
@agg_func = N'SUM',
@agg_col  = N'Quantity*UnitPrice';

EXEC pubs.dbo.sp_pivot
@query    = N'
SELECT stor_id, YEAR(ord_date) AS oy, MONTH(ord_date) AS om, qty
FROM dbo.sales',
@on_rows  = N'stor_id',
@on_cols  = N'
CAST(oy AS VARCHAR(4)) + ''_''
+ RIGHT(''0'' + CAST(om AS VARCHAR(2)), 2)',
@agg_func = N'SUM',
@agg_col  = N'qty';