如何在SELECT查询中避免编写过多SQL CASE语句实现部门列拼接

现有xyz表结构及数据如下：

id    ADivision     BDivision   CDivision     DDivision  EDivision     FDivision
1       0              1           0           0            1           0
2       1              1           0           0            1           1

期望将各Division列中值为1的列名前缀（A/B/C/D/E/F）提取出来，用-连接，得到如下格式的输出：

id    Divisions
1     B-E
2     A-B-E-F

此前尝试用CASE语句实现，但需要编写大量分支，询问是否有更简洁的实现方法。

解决方案 #

不需要写大量CASE分支，可通过条件拼接字符串或行转列后聚合的方式实现，不同数据库的函数略有差异，以下是几种常用数据库的实现方式：

1. MySQL/MariaDB #

利用CONCAT_WS函数（自动忽略NULL值），结合IF判断每列是否为1，是则返回对应前缀，否则返回NULL：

SELECT 
    id,
    CONCAT_WS('-',
        IF(ADivision = 1, 'A', NULL),
        IF(BDivision = 1, 'B', NULL),
        IF(CDivision = 1, 'C', NULL),
        IF(DDivision = 1, 'D', NULL),
        IF(EDivision = 1, 'E', NULL),
        IF(FDivision = 1, 'F', NULL)
    ) AS Divisions
FROM xyz;

CONCAT_WS会自动跳过NULL值，只将非NULL的前缀用-连接，避免出现多余分隔符。

2. SQL Server #

方式一：行转列后聚合

使用STRING_AGG结合UNPIVOT将列转为行，筛选值为1的记录后拼接：

SELECT 
    id,
    STRING_AGG(Division, '-') WITHIN GROUP (ORDER BY Division) AS Divisions
FROM (
    SELECT 
        id,
        CASE col 
            WHEN 'ADivision' THEN 'A'
            WHEN 'BDivision' THEN 'B'
            WHEN 'CDivision' THEN 'C'
            WHEN 'DDivision' THEN 'D'
            WHEN 'EDivision' THEN 'E'
            WHEN 'FDivision' THEN 'F'
        END AS Division
    FROM xyz
    UNPIVOT (
        Val FOR col IN (ADivision, BDivision, CDivision, DDivision, EDivision, FDivision)
    ) AS unpvt
    WHERE Val = 1
) AS temp
GROUP BY id;

方式二：条件拼接后清理前缀

用CONCAT结合IIF拼接，再通过STUFF去掉开头多余的-：

SELECT 
    id,
    STUFF(
        CONCAT(
            IIF(ADivision = 1, '-A', ''),
            IIF(BDivision = 1, '-B', ''),
            IIF(CDivision = 1, '-C', ''),
            IIF(DDivision = 1, '-D', ''),
            IIF(EDivision = 1, '-E', ''),
            IIF(FDivision = 1, '-F', '')
        ), 1, 1, ''
    ) AS Divisions
FROM xyz;

3. PostgreSQL #

方式一：JSON行转列后聚合

借助JSONB_EACH_TEXT将列转为键值对，筛选值为1的记录后拼接：

SELECT 
    id,
    STRING_AGG(LEFT(key, 1), '-' ORDER BY LEFT(key, 1)) AS Divisions
FROM xyz,
     JSONB_EACH_TEXT(to_jsonb(xyz) - 'id') AS t(key, val)
WHERE val = '1'
GROUP BY id;

方式二：简洁CASE拼接

用CONCAT_WS结合简化的CASE判断（无需分支嵌套）：

SELECT 
    id,
    CONCAT_WS('-',
        CASE WHEN ADivision = 1 THEN 'A' END,
        CASE WHEN BDivision = 1 THEN 'B' END,
        CASE WHEN CDivision = 1 THEN 'C' END,
        CASE WHEN DDivision = 1 THEN 'D' END,
        CASE WHEN EDivision = 1 THEN 'E' END,
        CASE WHEN FDivision = 1 THEN 'F' END
    ) AS Divisions
FROM xyz;

内容的提问来源于stack exchange，提问作者coder rock