动态数据透视查询

要使PIVOT动态，您必须编写一个存储过程。

CREATE PROCEDURE dynPivot (@select varchar(255), @pivotColumns varchar(255), @summaries varchar(100) ) ASDECLARE @pivot varchar(max), @sql varchar(max), @selectForPivotColumns varchar(max)SELECT @selectForPivotColumns = REPLACE(@select, 'SELECT', 'SELECT ' + @pivotColumns + ' AS rowValuesToColumns, ')CREATE TABLE #pivot_columns (pivot_column varchar(100))SELECT @sql = 'SELECT DISTINCT rowValuesToColumns FROM (' + @selectForPivotColumns + ') as t'INSERT INTO #pivot_columnsEXEC(@sql)SELECT @pivot = COALESCE(@pivot + ', ', '') + '[' + pivot_column + ']' FROM #pivot_columnsSELECt @sql = '    SELECT * FROM    (        ' + @select + '    ) AS t    PIVOT    (        ' + @summaries + ' FOR ' + @pivotColumns + ' IN (' + @pivot + ')    ) AS p'EXEC(@sql)

然后，您可以这样称呼它：

EXEC dynPivot 'SELECT * FROM tbl_DeductionHead', 'DeductionHead', 'SUM(DeductionPerAmount)';