SQL Server中如何将ID、Name/Value记录从行转置为列

问题场景 #

使用SQL Server单表tblObject存储产品及其属性，无法修改表结构。每个prodId对应多个fieldName（如name、description、status等，支持扩展），需要将行转列得到指定格式的结果。已尝试多个示例但无效，误以为不需要SUM/MAX这类聚合函数（实际因唯一值，聚合操作不影响最终结果）。

示例表（tblObject） #

期望输出 #

当前尝试的无效SQL #

SELECT * FROM (
  SELECT prodId, fieldName, fieldValue
  FROM tblObjects
) Results
PIVOT (
  fieldValue
  FOR fieldName
  IN (SELECT fieldName from tblObjects GROUP BY fieldName)
  )
) AS PivotTable

解决方案 #

核心逻辑说明 #

SQL Server的PIVOT语法必须搭配聚合函数，但你的场景中每个prodId+fieldName组合只有唯一的fieldValue，使用MAX()或MIN()不会改变结果，仅用于满足语法要求。同时，动态扩展的列需要用动态SQL实现，因为IN子句中不能直接嵌套子查询。

动态SQL实现代码 #

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 拼接所有需要转置的fieldName为带引号的逗号分隔字符串
SET @cols = STUFF((SELECT ',' + QUOTENAME(fieldName)
                  FROM tblObject
                  GROUP BY fieldName
                  ORDER BY fieldName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

-- 构建动态查询语句
SET @query = N'
SELECT 
    prodId,
    name AS prodName, -- 复用name字段值生成prodName列
    ' + @cols + ',
    ''2022-10-27'' AS createdDate -- 固定日期，可按需替换为GETDATE()或其他逻辑
FROM (
    SELECT prodId, fieldName, fieldValue
    FROM tblObject
) AS src
PIVOT (
    MAX(fieldValue) -- 聚合不影响结果，仅满足PIVOT语法要求
    FOR fieldName IN (' + @cols + ')
) AS piv
'

-- 执行动态SQL
EXEC sp_executesql @query

代码细节说明 #

1. 动态列拼接：通过STUFF和FOR XML PATH将所有fieldName转换为[name],[description],[status]格式的字符串，适配PIVOT的语法要求。
2. 聚合函数使用：MAX(fieldValue)仅作为语法填充，因每个prodId对应单个fieldName只有唯一值，聚合后结果与原fieldValue完全一致。
3. prodName列：直接将name字段重命名为prodName，实现需求中的重复列展示。
4. createdDate：示例中使用固定日期'2022-10-27'，如果需要动态生成当前日期，可替换为GETDATE()。

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