TSQL动态多列Pivot实现：学生联系人列转行报错求助

修正后的动态SQL代码（SQL Server） #

假设你的parentContact表包含学生基础字段（如STU_ID、SCH_YEAR、STU_NAME、GRADE）和联系人字段（ContactName、CellPhone），以下代码可实现动态将同一学生同一年度的多行联系人信息转为多列：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 生成带序号的联系人数据集
WITH ContactRN AS (
    SELECT 
        STU_ID, SCH_YEAR, STU_NAME, GRADE,
        ContactName, CellPhone,
        -- 按学生+年度分组，给联系人编序号
        ROW_NUMBER() OVER(PARTITION BY STU_ID, SCH_YEAR ORDER BY ContactName) AS RN
    FROM parentContact
)
-- 动态拼接需要生成的联系人列（ContactName1、CellPhone1...）
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN RN = ', RN, ' THEN ContactName END) AS ContactName', RN, ',',
        'MAX(CASE WHEN RN = ', RN, ' THEN CellPhone END) AS CellPhone', RN
    ),
    ','
)
FROM (SELECT DISTINCT RN FROM ContactRN) t

-- 构建并执行最终查询
SET @sql = CONCAT(
'WITH ContactRN AS (
    SELECT 
        STU_ID, SCH_YEAR, STU_NAME, GRADE,
        ContactName, CellPhone,
        ROW_NUMBER() OVER(PARTITION BY STU_ID, SCH_YEAR ORDER BY ContactName) AS RN
    FROM parentContact
)
SELECT 
    STU_ID, SCH_YEAR, STU_NAME, GRADE,
    ', @cols, '
FROM ContactRN
GROUP BY STU_ID, SCH_YEAR, STU_NAME, GRADE'
)

EXEC sp_executesql @sql

针对低版本SQL Server（2016及以下）的兼容修改 #

如果你的SQL Server版本不支持STRING_AGG，替换动态列拼接部分为：

SELECT @cols = STUFF((
    SELECT ',' + CONCAT(
        'MAX(CASE WHEN RN = ', RN, ' THEN ContactName END) AS ContactName', RN, ',',
        'MAX(CASE WHEN RN = ', RN, ' THEN CellPhone END) AS CellPhone', RN
    )
    FROM (SELECT DISTINCT RN FROM ContactRN) t
    ORDER BY RN
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

常见错误修正说明 #

1. 避免误用PIVOT语法：原生PIVOT仅支持单个聚合列，无法同时处理ContactName和CellPhone这类多字段转列需求，用MAX(CASE...)的方式更灵活且不易出错。
2. 序号生成逻辑：必须用PARTITION BY STU_ID, SCH_YEAR确保同一学生同一年度的联系人被正确编号，ORDER BY可替换为实际业务排序字段（如联系人创建时间）来固定列顺序。
3. 动态列拼接：确保拼接的列名格式统一，且覆盖所有可能的联系人序号，避免遗漏或语法错误。

扩展说明 #

如果需要新增其他联系人字段（如Email、Relation），只需在CASE WHEN部分添加对应字段的处理：

CONCAT(
    'MAX(CASE WHEN RN = ', RN, ' THEN ContactName END) AS ContactName', RN, ',',
    'MAX(CASE WHEN RN = ', RN, ' THEN CellPhone END) AS CellPhone', RN, ',',
    'MAX(CASE WHEN RN = ', RN, ' THEN Email END) AS Email', RN, ',',
    'MAX(CASE WHEN RN = ', RN, ' THEN Relation END) AS Relation', RN
)

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