MS SQL跨列值统计：将T1表唯一值转为列并汇总计数

嘿，这需求很明确！要把T1表里三列的R、G、B统计总次数，转成以它们为列名的结果表，我给你几种实用的解法，覆盖不同数据库场景：

通用兼容解法（所有SQL数据库都支持） #

这个方法用UNION ALL把三列的数据合并成单列，再通过条件聚合统计每个值的次数，兼容性拉满：

SELECT
  SUM(CASE WHEN val = 'R' THEN 1 ELSE 0 END) AS R,
  SUM(CASE WHEN val = 'G' THEN 1 ELSE 0 END) AS G,
  SUM(CASE WHEN val = 'B' THEN 1 ELSE 0 END) AS B
FROM (
  -- 把C1、C2、C3的所有值合并到同一个临时列val里
  SELECT C1 AS val FROM T1
  UNION ALL
  SELECT C2 AS val FROM T1
  UNION ALL
  SELECT C3 AS val FROM T1
) AS all_values;

执行后就会返回一行数据：9 | 4 | 5，完全符合你要的结构。

用PIVOT简化写法（支持PIVOT的数据库：SQL Server、Oracle、PostgreSQL 11+） #

如果你的数据库支持PIVOT语法，可以用更简洁的写法：

SQL Server版

SELECT R, G, B
FROM (
  -- 先把三列转成键值对格式
  SELECT val FROM T1
  UNPIVOT (val FOR col IN (C1, C2, C3)) AS unpvt
) AS src
-- 把val的不同值转成列，统计数量
PIVOT (
  COUNT(val) FOR val IN (R, G, B)
) AS pvt;

PostgreSQL版（11+）

SELECT *
FROM (
  -- 用unnest把三列转成单列数据
  SELECT unnest(array[C1, C2, C3]) AS val FROM T1
) AS src
PIVOT (
  COUNT(val) FOR val IN ('R' AS R, 'G' AS G, 'B' AS B)
);

生成新表 #

如果需要直接创建符合要求的新表，只需要在查询前加上创建表的语句：

-- 通用创建新表的写法（MySQL、PostgreSQL等适用）
CREATE TABLE new_table AS
SELECT
  SUM(CASE WHEN val = 'R' THEN 1 ELSE 0 END) AS R,
  SUM(CASE WHEN val = 'G' THEN 1 ELSE 0 END) AS G,
  SUM(CASE WHEN val = 'B' THEN 1 ELSE 0 END) AS B
FROM (
  SELECT C1 AS val FROM T1
  UNION ALL
  SELECT C2 AS val FROM T1
  UNION ALL
  SELECT C3 AS val FROM T1
) AS all_values;

-- SQL Server可以用SELECT INTO
SELECT
  SUM(CASE WHEN val = 'R' THEN 1 ELSE 0 END) AS R,
  SUM(CASE WHEN val = 'G' THEN 1 ELSE 0 END) AS G,
  SUM(CASE WHEN val = 'B' THEN 1 ELSE 0 END) AS B
INTO new_table
FROM (
  SELECT C1 AS val FROM T1
  UNION ALL
  SELECT C2 AS val FROM T1
  UNION ALL
  SELECT C3 AS val FROM T1
) AS all_values;

内容的提问来源于stack exchange，提问作者Anupriya Jaiswal