SQL Server下为数据加虚拟标识并统计诊断编码各位置次数问询

方式1：基于你原本的UNPIVOT语法扩展实现 #

你原来的UNPIVOT逻辑已经可以拿到每个诊断编码对应的原始列位置，只需要在后续分组聚合的时候做条件计数即可，代码如下：

SELECT
    Diag AS Diagnosis,
    COUNT(*) AS [Total Count],
    SUM(CASE WHEN ColumnALL = 'Diagnosis1' THEN 1 ELSE 0 END) AS [Diag1 Count],
    SUM(CASE WHEN ColumnALL = 'Diagnosis2' THEN 1 ELSE 0 END) AS [Diag2 Count],
    SUM(CASE WHEN ColumnALL = 'Diagnosis3' THEN 1 ELSE 0 END) AS [Diag3 Count],
    SUM(CASE WHEN ColumnALL = 'Diagnosis 4' THEN 1 ELSE 0 END) AS [Diag4 Count]
FROM 你的实际表名
UNPIVOT (
    Diag FOR ColumnALL IN (Diagnosis1, Diagnosis2, Diagnosis3, [Diagnosis 4])
) AS unpvt
GROUP BY Diag
ORDER BY Diag

注意原表的第4个诊断列名带空格，需要用[]包裹避免语法报错。

方式2：无需行转列，直接条件聚合（性能更优） #

如果不需要中间行转列的结果，可以直接对原表按诊断编码维度聚合，代码更简洁：

SELECT
    Diagnosis,
    SUM(CountVal) AS [Total Count],
    SUM(Diag1Flag) AS [Diag1 Count],
    SUM(Diag2Flag) AS [Diag2 Count],
    SUM(Diag3Flag) AS [Diag3 Count],
    SUM(Diag4Flag) AS [Diag4 Count]
FROM (
    SELECT Diagnosis1 AS Diagnosis, 1 AS CountVal, 1 AS Diag1Flag, 0 AS Diag2Flag, 0 AS Diag3Flag, 0 AS Diag4Flag FROM 你的实际表名 WHERE Diagnosis1 IS NOT NULL
    UNION ALL
    SELECT Diagnosis2 AS Diagnosis, 1 AS CountVal, 0 AS Diag1Flag, 1 AS Diag2Flag, 0 AS Diag3Flag, 0 AS Diag4Flag FROM 你的实际表名 WHERE Diagnosis2 IS NOT NULL
    UNION ALL
    SELECT Diagnosis3 AS Diagnosis, 1 AS CountVal, 0 AS Diag1Flag, 0 AS Diag2Flag, 1 AS Diag3Flag, 0 AS Diag4Flag FROM 你的实际表名 WHERE Diagnosis3 IS NOT NULL
    UNION ALL
    SELECT [Diagnosis 4] AS Diagnosis, 1 AS CountVal, 0 AS Diag1Flag, 0 AS Diag2Flag, 0 AS Diag3Flag, 1 AS Diag4Flag FROM 你的实际表名 WHERE [Diagnosis 4] IS NOT NULL
) AS t
GROUP BY Diagnosis
ORDER BY Diagnosis

两种方式执行后得到的结果和你期望的输出结构完全一致，可根据实际使用场景选择。

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