sql - 有没有办法在 tSQL 的不同列中汇总分组？

Question

我有几列具有不同数量的相同值：

+---+---+---+
| A | B | C |
+---+---+---+
| 1 | 4 | 4 |
| 2 | 4 | 3 |
| 2 | 4 | 3 |
| 4 | 4 | 3 |
| 3 | 2 | 3 |
| 3 | 1 | 2 |
| 3 | 3 | 2 |
| 1 | 1 | 1 |
+---+---+---+

为此，我想计算每个值的总数并返回如下输出：

+---+---+---+---+
|   | A | B | C |
+---+---+---+---+
| 1 | 2 | 2 | 1 |
| 2 | 2 | 1 | 2 |
| 3 | 3 | 1 | 4 |
| 4 | 1 | 4 | 1 |
+---+---+---+---+

据我所知，枢轴操作仅允许您计算单个列中的响应数。

任何帮助感激不尽！

干杯

标签： sqlsql-servertsqlpivot

Answer 1

您可以使用apply：

select colv, 
       sum(case when colname = 'A' then 1 else 0 end) as [A],
       sum(case when colname = 'B' then 1 else 0 end) as [B],
       sum(case when colname = 'C' then 1 else 0 end) as [C]
from table t cross apply
    ( values ('A', A), ('B', B), ('C', C) 
    ) tt (colname, colv)
group by colv;