SQL Server中含无数据列的PIVOT查询构建方法咨询

先明确你的需求：把每个[SOC_NUM, CLS_NUM, CAT_NUM]组合下的不同QERROR值，从行转成Error 1、Error 2这样的列，方便后续筛选同时存在多种状态的组合。

核心逻辑拆解 #

PIVOT的本质是将行中的离散取值转换为列，要实现这个需要先给每个分组内的QERROR标记序号（比如1、2），让SQL明确哪个行值对应哪个目标列。

完整查询语句 #

SELECT 
    SOC_NUM, 
    CLS_NUM, 
    CAT_NUM,
    [1] AS [Error 1],
    [2] AS [Error 2]
FROM (
    -- 子查询：给每个组合下的QERROR标记序号
    SELECT 
        SOC_NUM, 
        CLS_NUM, 
        CAT_NUM, 
        QERROR,
        -- 按组合分组，给每个QERROR分配序号
        ROW_NUMBER() OVER(
            PARTITION BY SOC_NUM, CLS_NUM, CAT_NUM 
            ORDER BY QERROR -- 可根据需求调整排序规则，比如按CNT DESC
        ) AS Seq
    FROM (
        -- 你的原始分组查询
        SELECT 
            SOC_NUM, 
            CLS_NUM, 
            CAT_NUM, 
            QERROR, 
            SUM(1) AS CNT
        FROM dbo.AS_Data_Disjoint_RPTS (nolock)
        WHERE ReportName='NBN_Errors' AND QERROR IS NOT NULL
        GROUP BY SOC_NUM, CLS_NUM, CAT_NUM, QERROR
    ) AS GroupedData
) AS NumberedData
-- PIVOT：将Seq的取值（1、2）转成列，对应QERROR的值
PIVOT (
    MAX(QERROR) -- 因为每个Seq在分组内唯一，MAX/ MIN都可以
    FOR Seq IN ([1], [2])
) AS PivotedResult
ORDER BY SOC_NUM, CLS_NUM, CAT_NUM;

各部分说明 #

1. 最内层子查询：保留你原本的分组统计逻辑，生成带计数的基础数据集。
2. 中间子查询：用ROW_NUMBER()给每个[SOC_NUM, CLS_NUM, CAT_NUM]组合下的QERROR标记序号，比如某个组合有2种QERROR，就会生成Seq=1和Seq=2的两行。
3. PIVOT部分：指定用MAX(QERROR)聚合（因每个Seq在分组里唯一，MAX/MIN不影响结果），把Seq的取值[1]、[2]转成列，最后重命名为Error 1、Error 2。

后续筛选（直接找多状态组合） #

如果要直接筛选同时存在两种状态的组合，只需在查询末尾加WHERE [Error 2] IS NOT NULL：

SELECT 
    SOC_NUM, 
    CLS_NUM, 
    CAT_NUM,
    [1] AS [Error 1],
    [2] AS [Error 2]
FROM (
    SELECT 
        SOC_NUM, 
        CLS_NUM, 
        CAT_NUM, 
        QERROR,
        ROW_NUMBER() OVER(
            PARTITION BY SOC_NUM, CLS_NUM, CAT_NUM 
            ORDER BY QERROR
        ) AS Seq
    FROM (
        SELECT 
            SOC_NUM, 
            CLS_NUM, 
            CAT_NUM, 
            QERROR, 
            SUM(1) AS CNT
        FROM dbo.AS_Data_Disjoint_RPTS (nolock)
        WHERE ReportName='NBN_Errors' AND QERROR IS NOT NULL
        GROUP BY SOC_NUM, CLS_NUM, CAT_NUM, QERROR
    ) AS GroupedData
) AS NumberedData
PIVOT (
    MAX(QERROR)
    FOR Seq IN ([1], [2])
) AS PivotedResult
WHERE [Error 2] IS NOT NULL
ORDER BY SOC_NUM, CLS_NUM, CAT_NUM;

示例结果验证 #

运行上述查询后，会得到你需要的格式：

SOC_NUM    CLS_NUM        CAT_NUM       Error 1                   Error 2
25113        25            TX9AW        NBN_ID_ERROR              NULL
25113        25            WI913        NBN_ID_ERROR              NULL
25257         9            TX9AW        NBN_ID_ERROR              No Error
...

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