从单表执行Pivot和Unpivot生成平行记录的技术实现问题

要实现将INCONSISTANCE、COMMENT、CORRECTION与RESULT形成平行记录的透视结构，有两种高效方式，适配不同场景：

方式一：逆透视+透视（UNPIVOT + PIVOT） #

这种方式适合SECTION取值较多或动态变化的场景，代码更简洁：

WITH UnpivotedData AS (
    SELECT 
        NIN,
        -- 标记当前行对应的字段类型
        RecordType = CASE FieldName 
            WHEN 'RESULT' THEN '检测结果'
            WHEN 'INCONSISTANCE' THEN '不一致记录'
            WHEN 'COMMENT' THEN '备注'
            WHEN 'CORRECTION' THEN '修正内容'
        END,
        SectionName = SECTION,
        -- 统一字段类型，避免类型不兼容问题
        FieldValue = CAST(FieldValue AS NVARCHAR(MAX))
    FROM 
        [dbo].[Observations]
    -- 把四个字段逆转为多行记录
    UNPIVOT (
        FieldValue FOR FieldName IN (RESULT, INCONSISTANCE, COMMENT, CORRECTION)
    ) AS Up
),
PivotedData AS (
    SELECT 
        NIN,
        RecordType,
        -- 替换为你实际的SECTION取值列表
        [BloodTest], [X-Ray], [ECG], [UrineTest]
    FROM 
        UnpivotedData
    -- 把SECTION值透视成列
    PIVOT (
        MAX(FieldValue) FOR SectionName IN ([BloodTest], [X-Ray], [ECG], [UrineTest])
    ) AS Pv
)
SELECT * FROM PivotedData ORDER BY NIN, RecordType;

逻辑说明 #

1. UnpivotedData：将原表中每个NIN+SECTION组合下的4个字段拆分为4行，每行对应一个字段类型（比如"检测结果"对应RESULT字段值），同时统一字段类型避免透视时报错。
2. PivotedData：将拆分后的SectionName（原SECTION字段值）透视成列，每个NIN+RecordType组合对应一行，各SECTION列填充对应字段的内容。

方式二：扩展原有CASE逻辑（适合固定SECTION取值） #

如果你的SECTION取值固定，可直接基于你已有的CASE透视逻辑扩展，无需使用UNPIVOT：

WITH BaseData AS (
    SELECT 
        NIN,
        SECTION,
        RESULT,
        INCONSISTANCE,
        COMMENT,
        CORRECTION
    FROM [dbo].[Observations]
),
UnionRecords AS (
    -- 生成RESULT对应的行
    SELECT 
        NIN,
        RecordType = '检测结果',
        CASE WHEN SECTION = 'BloodTest' THEN CAST(RESULT AS NVARCHAR(MAX)) END AS [BloodTest],
        CASE WHEN SECTION = 'X-Ray' THEN CAST(RESULT AS NVARCHAR(MAX)) END AS [X-Ray],
        CASE WHEN SECTION = 'ECG' THEN CAST(RESULT AS NVARCHAR(MAX)) END AS [ECG]
    FROM BaseData
    UNION ALL
    -- 生成INCONSISTANCE对应的行
    SELECT 
        NIN,
        RecordType = '不一致记录',
        CASE WHEN SECTION = 'BloodTest' THEN INCONSISTANCE END AS [BloodTest],
        CASE WHEN SECTION = 'X-Ray' THEN INCONSISTANCE END AS [X-Ray],
        CASE WHEN SECTION = 'ECG' THEN INCONSISTANCE END AS [ECG]
    FROM BaseData
    UNION ALL
    -- 生成COMMENT对应的行
    SELECT 
        NIN,
        RecordType = '备注',
        CASE WHEN SECTION = 'BloodTest' THEN COMMENT END AS [BloodTest],
        CASE WHEN SECTION = 'X-Ray' THEN COMMENT END AS [X-Ray],
        CASE WHEN SECTION = 'ECG' THEN COMMENT END AS [ECG]
    FROM BaseData
    UNION ALL
    -- 生成CORRECTION对应的行
    SELECT 
        NIN,
        RecordType = '修正内容',
        CASE WHEN SECTION = 'BloodTest' THEN CORRECTION END AS [BloodTest],
        CASE WHEN SECTION = 'X-Ray' THEN CORRECTION END AS [X-Ray],
        CASE WHEN SECTION = 'ECG' THEN CORRECTION END AS [ECG]
    FROM BaseData
)
-- 聚合同一NIN+RecordType下的结果，得到完整行
SELECT 
    NIN,
    RecordType,
    MAX([BloodTest]) AS [BloodTest],
    MAX([X-Ray]) AS [X-Ray],
    MAX([ECG]) AS [ECG]
FROM UnionRecords
GROUP BY NIN, RecordType
ORDER BY NIN, RecordType;

逻辑说明 #

1. BaseData：获取原表基础数据，可复用你已有的CTE。
2. UnionRecords：分别为四个字段类型生成对应的CASE列，用UNION ALL合并所有临时行。
3. 最后通过GROUP BY NIN, RecordType，用MAX()聚合同一类型下的各SECTION值，将分散的CASE结果合并为完整的一行。

注意事项 #

• 若SECTION取值不固定，可使用动态SQL自动生成透视列（拼接SECTION值列表）。
• 所有字段需统一为字符串类型（如CAST(RESULT AS NVARCHAR(MAX))），避免透视时因类型不一致报错。

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