动态Pivot(1)

USE master;GO

IF OBJECT_ID('dbo.sp_pivot') IS NOT NULLDROP 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 NULLSET @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, UnitPriceFROM dbo.Orders AS OJOIN dbo.[Order Details] AS ODON 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, qtyFROM 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';