如何使用select查询获得不写入更多SQL CASES的输出

Question

我有以下xyz表数据

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

我想要像下面这样的输出

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

我尝试使用切换案例，但需要写更多案例

获得输出的任何其他方法

Answer 1

CONCAT_WS()函数是您的朋友：

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 yourTable
ORDER BY id;

以下是可能不支持CONCAT_WS():的早期版本SQL Server的解决方法

SELECT
id,
STUFF(
COALESCE('-' + CASE WHEN ADivision = 1 THEN 'A' END, '') +
COALESCE('-' + CASE WHEN BDivision = 1 THEN 'B' END, '') +
COALESCE('-' + CASE WHEN CDivision = 1 THEN 'C' END, '') +
COALESCE('-' + CASE WHEN DDivision = 1 THEN 'D' END, '') +
COALESCE('-' + CASE WHEN EDivision = 1 THEN 'E' END, '') +
COALESCE('-' + CASE WHEN FDivision = 1 THEN 'F' END, '')
1, 2, '') AS Divisions
FROM yourTable
ORDER BY id;