如何在动态Pivot（透视）操作后执行多表关联？

问题背景 #

已有静态透视SQL可将行转成列，但实际场景需要动态生成透视列，同时要把透视后的ID列名替换为对应的自定义字段名称，还要关联获取字段值的实际文本（比如把5000替换成North Carolina），最终得到字段名与实际值对应结果集。

现有静态透视查询示例：

select cj.JobId, cj.JobTitle, i.[100],i.[101],i.[110],i.[120]
from ContractJobs cj
inner join (
select * from
(select JobId, CustomFieldId, CustomFieldValue from ContractJobCustomFields) cjcf
PIVOT
(MAX(CustomFieldValue) FOR CustomFieldId in ([100],[101],[110],[120])) p
) i on cj.JobId = i.JobId

现有支撑表结构：

CustomFields表 #

CustomFieldId | CustomFieldName
-------------------------------
100           | Location
101           | Type
110           | Source
120           | Branch

CustomFieldListValues表 #

ID   |  CustomFieldID | CustomFieldValue
-----------------------------
5000 | 100            | North Carolina
5001 | 100            | South Carolina
5100 | 101            | Retail
5102 | 101            | Commercial

需要实现的最终结果：

JobId | JobTitle  | Location        | Type        | Source  | Branch
--------------------------------------------------------------------
1     | Janitor   | North Carolina  | Retail      | etc...  | etc...
2     | Cook      | South Carolina  | Commercial  | etc...  | etc...

实现方案 #

要完成动态透视+列名替换+值关联，需用动态SQL实现，核心思路是先获取自定义字段的ID和名称，动态拼接透视列与关联逻辑。

完整动态SQL代码 #

DECLARE @PivotColumns NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

-- 1. 动态生成透视列（用CustomFieldName作为列名）
SELECT @PivotColumns = STRING_AGG(QUOTENAME(CustomFieldName), ', ')
FROM CustomFields

-- 2. 拼接完整的动态SQL语句
SET @SQL = N'
SELECT cj.JobId, cj.JobTitle, ' + @PivotColumns + N'
FROM ContractJobs cj
INNER JOIN (
    SELECT JobId, cf.CustomFieldName, clv.CustomFieldValue
    FROM ContractJobCustomFields cjcf
    INNER JOIN CustomFields cf ON cjcf.CustomFieldId = cf.CustomFieldId
    INNER JOIN CustomFieldListValues clv ON cjcf.CustomFieldValue = clv.ID AND cf.CustomFieldId = clv.CustomFieldID
    PIVOT (
        MAX(CustomFieldValue) FOR CustomFieldName IN (' + @PivotColumns + N')
    ) AS PivotTable
) AS PivotData ON cj.JobId = PivotData.JobId'

-- 3. 执行动态SQL
EXEC sp_executesql @SQL

关键说明 #

• 低版本SQL Server兼容：如果使用2017以下版本，STRING_AGG函数不可用，可改用FOR XML PATH拼接列列表：

  SELECT @PivotColumns = STUFF((SELECT ', ' + QUOTENAME(CustomFieldName)
                                FROM CustomFields
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
  

• 聚合函数选择：透视必须用聚合函数，这里用MAX(CustomFieldValue)是因为每个JobId+CustomFieldName只会对应一个值，MAX不会改变结果。
• 关联准确性：关联时需同时匹配cjcf.CustomFieldValue = clv.ID和cf.CustomFieldId = clv.CustomFieldID，避免不同字段的ID重复导致关联错误。

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