使用临时表实现Dynamic Pivot 解决医疗诊断数据行转列合并需求

问题1：临时表对象无效 #

• 原因：局部临时表（以#开头）的作用域仅限创建它的会话/批次，你在sp_executesql执行的动态SQL内部创建的#PROCEDURES_DX_PIVOT，会在动态SQL执行结束后自动销毁，外部会话无法访问。
• 解决方法二选一：
  1. 改用全局临时表（以##开头），注意使用后手动销毁避免冲突：把动态SQL里的#PROCEDURES_DX_PIVOT改成##PROCEDURES_DX_PIVOT
  2. 将后续对该临时表的查询逻辑也放到动态SQL内部执行

问题2：同患者诊断拆分多行，且缺失描述字段 #

• 原因1：PIVOT操作默认将源表中所有未出现在聚合函数、FOR子句中的字段作为分组依据，你的源表#PROCEDURES_DX里还包含dx_eff_full_date、dx_type_desc、prio_cd、clasf_desc、Procedure_Type等字段，这些字段值不同就会导致同一患者被拆分为多行。
• 原因2：当前PIVOT仅实现了诊断编码的行转列，未包含诊断描述的转换，不符合需求。

最优实现方案（条件聚合，比PIVOT更灵活适配多字段转列） #

直接用动态拼接条件聚合的方式实现，代码更易调试，也不会出现分组异常的问题：

-- 先获取最大诊断序号，生成动态列
DECLARE @MaxDxRank INT, @Sql NVARCHAR(MAX)
SELECT @MaxDxRank = MAX(CAST(RIGHT(Dx_Rank, LEN(Dx_Rank)-3) AS INT)) FROM #PROCEDURES_DX

-- 生成动态查询SQL
;WITH NumTally AS (
    SELECT 1 AS n UNION ALL SELECT n+1 FROM NumTally WHERE n < @MaxDxRank
)
SELECT @Sql = N'SELECT pt_id, vst_key, ' + 
STRING_AGG(
    'MAX(CASE WHEN Dx_Rank = ''Dx_'+CAST(n AS VARCHAR)+''' THEN dx_cd END) AS Dx_'+CAST(n AS VARCHAR)+', 
     MAX(CASE WHEN Dx_Rank = ''Dx_'+CAST(n AS VARCHAR)+''' THEN clasf_desc END) AS Dx_'+CAST(n AS VARCHAR)+'_Description',
     ', '
) + N' INTO ##PROCEDURES_DX_PIVOT 
FROM #PROCEDURES_DX 
GROUP BY pt_id, vst_key'
FROM NumTally

-- 执行动态SQL
EXEC sp_executesql @Sql

-- 外部可以正常访问全局临时表
SELECT * FROM ##PROCEDURES_DX_PIVOT

-- 用完记得销毁全局临时表
-- DROP TABLE IF EXISTS ##PROCEDURES_DX_PIVOT

如果使用的SQL Server版本低于2017不支持STRING_AGG，可以替换为原有COALESCE的方式拼接动态列即可。
同样的逻辑可以直接复用在CPT、DRG两类表的行转列需求里，只需要替换对应的字段名和排名前缀即可。

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