sql server 动态参数化 行转列

原文：https://www.cnblogs.com/gaizai/p/3753296.html、
修改：添加了列排序

DECLARE @sql_str NVARCHAR(MAX);
DECLARE @sql_col NVARCHAR(MAX);
DECLARE @tableName sysname; --行转列表
DECLARE @groupColumn sysname; --分组字段
DECLARE @row2column sysname; --行变列的字段
DECLARE @row2columnValue sysname; --行变列值的字段
DECLARE @sort2column sysname; --行变列值的字段
SET @tableName = ‘#tmp2‘;
SET @groupColumn = ‘ut_name‘;
SET @row2column = ‘code_name‘;
SET @row2columnValue = ‘je‘;
SET @sort2column = ‘disp_order‘;
--从行数据中获取可能存在的列
SET @sql_str = N‘
SELECT @sql_col_out = ISNULL(@sql_col_out + ‘‘,‘‘,‘‘‘‘) + QUOTENAME([‘ + @row2column + ‘]) 
    FROM [‘ + @tableName + ‘] GROUP BY [‘ + @row2column + ‘],[‘ + @sort2column + ‘] order by [‘ + @sort2column + ‘ ]‘;
--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
      + ‘]) p PIVOT 
    (SUM([‘ + @row2columnValue + ‘]) FOR [‘ + @row2column + ‘] IN ( ‘ + @sql_col + ‘) ) AS pvt 
ORDER BY pvt.[‘ + @groupColumn + ‘]‘;
--PRINT (@sql_str)
EXEC (@sql_str);

sql server 动态参数化 行转列

标签：sys   添加   tps   htm   分组   serve   arc   html   varchar