sql - 使用 SQL Server 聚合 bool 表

Question

我有这张 table 。所有值为 0 或 1。

<表类="s-表"><头>一个bc<正文>100110010111

我想要这个

<表类="s-表"><头><日> 一个bc<正文>一个321b231c111

最后一张表回答了多少行的 {raw} 和 {col} 设置为 1 的问题。例如，第一个表中有 2 行 a = b = 1，因此 cell(a,b) = 2.

我有一个不适合大表的查询。有没有可能让它变得更简单？

SELECT
    'a' AS ' ',  
    SUM(a) AS a, 
    (SELECT SUM(b) FROM tab WHERE a = 1) AS b, 
    (SELECT SUM(c) FROM tab WHERE a = 1) AS c 
FROM 
    tab

UNION

SELECT
    'b', 
    (SELECT SUM(a) FROM tab WHERE b = 1),
    SUM(b), 
    (SELECT SUM(c) FROM tab WHERE b = 1) 
FROM
    tab

UNION

SELECT
    'c', 
    (SELECT SUM(a) FROM tab WHERE c = 1), 
    (SELECT SUM(b) FROM tab WHERE c = 1),
    SUM(c) 
FROM
    tab

Answer 1

您可以使用条件聚合而不是子查询尝试以下操作，它可能更简洁:

select 'a' [ ],
 Sum (case when a = a then a else 0 end) a,
 Sum (case when a = b then b else 0 end) b,
 Sum (case when a = c then c else 0 end) c
from t 
union all
select 'b' [ ],
 Sum (case when b = a then a else 0 end) a,
 Sum (case when b = b then b else 0 end) b,
 Sum (case when b = c then c else 0 end) c
from t 
union all
select 'c' [ ],
 Sum (case when c = a then a else 0 end) a,
 Sum (case when c = b then b else 0 end) b,
 Sum (case when c = c then c else 0 end) c
from t;

DB<>Fiddle