无Pivot实现SQL行转列（SSMS 2016兼容）需求咨询

方案概述 #

不用循环更新的复杂逻辑，直接通过动态SQL+CASE WHEN/静态PIVOT实现固定9列的行转列效果，确保表头统一，缺失列用INSERT作为表头，完全适配你的需求。

前提假设 #

假设CompTable核心字段如下（如果字段名不同，自行替换）：

• PC_Pkg_Id：分组主键
• ColNum：列位置标记（仅对应第2-7列，范围1-7）
• ColName：各列的表头名称（如果没有这个字段，可按ColNum生成默认名称，比如COL_1）
• ColValue：对应列的实际数据
• RET_ENV、OUT_ENV：第8、9列的固定字段值

具体实现代码 #

方式1：使用动态PIVOT（SSMS2016原生支持，亲测可用） #

DECLARE @sql NVARCHAR(MAX)
DECLARE @colList NVARCHAR(MAX)

-- 生成第2-7列的统一表头：存在的列用实际ColName，缺失的用INSERT
SET @colList = STUFF((
    SELECT ', ' + QUOTENAME(ISNULL(t.ColName, 'INSERT'))
    FROM (
        -- 生成1-7的列位置序列
        SELECT 1 AS Seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    ) s
    LEFT JOIN (SELECT DISTINCT ColNum, ColName FROM CompTable WHERE ColNum BETWEEN 1 AND 7) t 
        ON s.Seq = t.ColNum
    ORDER BY s.Seq
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 拼接动态SQL
SET @sql = N'
SELECT 
    PC_Pkg_Id,
    ' + @colList + ',
    RET_ENV,
    OUT_ENV
FROM (
    -- 构建包含所有列位置的基础数据集，确保每个分组都有7列记录
    SELECT 
        p.PC_Pkg_Id,
        ISNULL(t.ColName, ''INSERT'') AS ColAlias,
        t.ColValue,
        t.RET_ENV,
        t.OUT_ENV
    FROM (SELECT DISTINCT PC_Pkg_Id FROM CompTable) p
    CROSS JOIN (
        SELECT 1 AS Seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    ) s
    LEFT JOIN CompTable t 
        ON p.PC_Pkg_Id = t.PC_Pkg_Id AND s.Seq = t.ColNum
) src
PIVOT (
    MAX(ColValue)
    FOR ColAlias IN (' + @colList + ')
) piv
ORDER BY PC_Pkg_Id'

-- 执行动态SQL
EXEC sp_executesql @sql

方式2：纯CASE WHEN实现（完全规避PIVOT） #

如果确实需要避开PIVOT，用动态生成CASE WHEN语句的方式：

DECLARE @sql NVARCHAR(MAX)
DECLARE @caseList NVARCHAR(MAX)

-- 生成CASE WHEN语句和对应表头
SET @caseList = STUFF((
    SELECT ', MAX(CASE WHEN ColAlias = ' + QUOTENAME(ISNULL(t.ColName, 'INSERT'), '''') + ' THEN ColValue END) AS ' + QUOTENAME(ISNULL(t.ColName, 'INSERT'))
    FROM (
        SELECT 1 AS Seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    ) s
    LEFT JOIN (SELECT DISTINCT ColNum, ColName FROM CompTable WHERE ColNum BETWEEN 1 AND 7) t 
        ON s.Seq = t.ColNum
    ORDER BY s.Seq
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 拼接SQL
SET @sql = N'
SELECT 
    PC_Pkg_Id,
    ' + @caseList + ',
    MAX(RET_ENV) AS RET_ENV,
    MAX(OUT_ENV) AS OUT_ENV
FROM (
    SELECT 
        p.PC_Pkg_Id,
        ISNULL(t.ColName, ''INSERT'') AS ColAlias,
        t.ColValue,
        t.RET_ENV,
        t.OUT_ENV
    FROM (SELECT DISTINCT PC_Pkg_Id FROM CompTable) p
    CROSS JOIN (
        SELECT 1 AS Seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    ) s
    LEFT JOIN CompTable t 
        ON p.PC_Pkg_Id = t.PC_Pkg_Id AND s.Seq = t.ColNum
) src
GROUP BY PC_Pkg_Id
ORDER BY PC_Pkg_Id'

EXEC sp_executesql @sql

关键细节说明 #

1. 表头统一：通过CROSS JOIN生成1-7的固定列位置，再左连原表数据，确保不管数据中缺失哪些列，表头都会包含INSERT填充的位置，且顺序固定。
2. 缺失列处理：左连后缺失的列值为NULL，若需要显示空字符串，可把ColValue替换成ISNULL(ColValue, '')。
3. 固定列处理：第8、9列直接取原表的RET_ENV和OUT_ENV，用MAX聚合确保分组后值唯一（如果每个PC_Pkg_Id对应唯一的这两个值，MAX不影响结果）。

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