SQL Server多列值统计及行列转置后插入新表的实现咨询

假设源表名为SourceTable，待插入的统计结果表名为ResultTable，可直接用以下SQL实现：

-- 可选：提前声明要筛选的日期，方便修改
DECLARE @FilterDate VARCHAR(8) = '20210901';

-- 如果目标表已存在，用INSERT INTO插入
INSERT INTO ResultTable (Source, Yes, No, NA, Date)
SELECT 
  Source,
  ISNULL(Yes, 0) AS Yes,
  ISNULL(No, 0) AS No,
  ISNULL(NA, 0) AS NA,
  @FilterDate AS Date
FROM (
  -- 第二步：按原列名、值分组统计出现次数
  SELECT 
    Source,
    Val,
    COUNT(*) AS Cnt
  FROM (
    -- 第一步：把A/B/C/D四列从列转成行
    SELECT Source, Val
    FROM SourceTable
    UNPIVOT (
      Val FOR Source IN (A, B, C, D)
    ) AS Unpvt
    WHERE Date = @FilterDate
  ) AS Tmp1
  GROUP BY Source, Val
) AS Tmp2
-- 第三步：把Yes/No/NA从行转成列，对应统计次数
PIVOT (
  SUM(Cnt) FOR Val IN ([Yes], [No], [NA])
) AS Pvt;

-- 如果目标表不存在，可直接用SELECT INTO创建并插入
/*
SELECT 
  Source,
  ISNULL(Yes, 0) AS Yes,
  ISNULL(No, 0) AS No,
  ISNULL(NA, 0) AS NA,
  @FilterDate AS Date
INTO ResultTable
FROM (
  SELECT 
    Source,
    Val,
    COUNT(*) AS Cnt
  FROM (
    SELECT Source, Val
    FROM SourceTable
    UNPIVOT (
      Val FOR Source IN (A, B, C, D)
    ) AS Unpvt
    WHERE Date = @FilterDate
  ) AS Tmp1
  GROUP BY Source, Val
) AS Tmp2
PIVOT (
  SUM(Cnt) FOR Val IN ([Yes], [No], [NA])
) AS Pvt;
*/

逻辑说明 #

• 核心用SQL Server的UNPIVOT+PIVOT语法实现行列转换：
  • 先用UNPIVOT将原来横向排列的A、B、C、D四列，转成纵向的「原列名（Source）- 列值（Val）」两行
  • 分组统计每个Source下每个Val的出现次数
  • 再用PIVOT把Val的三个枚举值Yes、No、NA转成列，对应值就是统计的次数，用ISNULL处理没有出现的取值，默认计数为0
• 筛选条件直接加在最内层的子查询中，提前过滤数据，减少后续计算量
• 如果需要统计多个日期的结果，只需把Date字段加到Tmp1的查询字段和GROUP BY分组字段中，去掉外层固定的@FilterDate即可

内容的提问来源于stack exchange，提问作者Mahendra Singh Rathore