SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）(二)

-- Author: <听风吹雨>

-- Create date: <2014.05.26>

-- Description: <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>

-- Blog:

-- =============================================

DECLARE @sql_str NVARCHAR(MAX)

DECLARE @sql_col NVARCHAR(MAX)

DECLARE @sql_where NVARCHAR(MAX)

DECLARE @tableName SYSNAME --行转列表

DECLARE @groupColumn SYSNAME --分组字段

DECLARE @row2column SYSNAME --行变列的字段

DECLARE @row2columnValue SYSNAME --行变列值的字段

SET @tableName = 'TestRows2Columns'

SET @groupColumn = 'UserName'

SET @row2column = 'Subject'

SET @row2columnValue = 'Source'

SET @sql_where = 'WHERE UserName = ''王五'''

--从行数据中获取可能存在的列

SET @sql_str = N'

SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])

FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'

--PRINT @sql_str

EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT

--PRINT @sql_col

SET @sql_str = N'

SELECT * FROM (

SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']'+@sql_where+') p PIVOT

(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt

ORDER BY pvt.['+@groupColumn+']'

--PRINT (@sql_str)

EXEC (@sql_str)

?