Original: Https://www.cnblogs.com/gaizai/p/3753296.html,
Modified: Added column sort
DECLARE @sql_str NVARCHAR (max);D eclare @sql_col NVARCHAR (max);D eclare @tableName sysname; --line to list declare @groupColumn sysname; --Group field declare @row2column sysname; --row Variable column field declare @row2columnValue sysname; --row Variable column value field declare @sort2column sysname; --row Variable column Value field set @tableName = ' #tmp2 '; SET @groupColumn = ' ut_name '; SET @row2column = ' code_name '; SET @row2columnValue = ' JE '; Set @sort2column = ' Disp_order ';---Get the column set that may exist from the row data @sql_str = N ' SELECT @sql_col_out = ISNULL (@sql_col_out + ",", ") + QUOTENAME ([' + @row2column + ']) from [' + @tableName + '] GROUP by [' + @row2column + '],[' + @sort2column + '] Order by [' + @sort2column + '] ';--print @sql_strEXEC sp_executesql @sql_str, N ' @sql_col_out NVARCHAR (MAX) O Utput ', @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 dynamic parameterized row to column