应用UNPIVOT脚本时SSRS动态列报表出现数据转换失败问题

现有脚本存在3个核心错误，是触发数据转换问题、动态表头失效的直接原因：

• 取列逻辑完全错误：你直接从业务表SalesOrderHeader取ShipDate的字段值当动态列名，会生成大量重复的日期列，且WHERE条件的逻辑完全无效，拿字段值和字段名做对比，无法正确筛选要转置的表字段
• UNPIVOT要求所有被转置的列数据类型完全一致，原脚本虽然写了CONVERT转换，但因为取列逻辑错误，仍会出现类型不匹配的转换报错
• 全局临时表##Temp会导致多用户并发跑SSRS报表时出现数据冲突，不同用户的请求会互相覆盖临时表数据

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp; 

DECLARE @ObjectName VARCHAR(100) = '[AdventureWorks2016].[Sales].[SalesOrderHeader]' ,
        @KeyColumn VARCHAR(100)       = '[SalesOrderID]'; -- 不需要转置的主键列名

DECLARE @ColumnNames NVARCHAR(MAX)= '' ,
        @Values NVARCHAR(MAX)= '' ,
        @SQL NVARCHAR(MAX)= '';

-- 从系统表读取目标表的所有字段，自动排除主键列
SELECT  @ColumnNames += ', ' + QUOTENAME(c.name) ,
        @Values += ', ' + QUOTENAME(c.name) + ' = CONVERT(VARCHAR(1000), ' + QUOTENAME(c.name) + ', 120)'
FROM    sys.columns c
WHERE   c.object_id = OBJECT_ID(@ObjectName)
        AND QUOTENAME(c.name) <> @KeyColumn;

SET @SQL = N'
SELECT * INTO #Temp
FROM
(
  SELECT ' + @KeyColumn + @Values + '
  FROM ' + @ObjectName + '
) AS DRV
UNPIVOT
(
  Value FOR ColumnName IN (' + STUFF(@ColumnNames, 1, 1, '') + ')
) AS UnPVT;

SELECT * FROM #Temp
';
EXEC sp_executesql @SQL;

• 将上述修复后的脚本填入SSRS数据集的查询框，数据集返回固定三个字段：主键字段（示例为SalesOrderID）、Value、ColumnName
• 报表中插入矩阵组件，行组绑定主键字段，列组绑定ColumnName字段，值单元格绑定Value字段
• 报表运行时会自动根据返回的ColumnName值生成动态表头，无需额外配置

内容的提问来源于stack exchange，提问作者hab