使用CASE语句进行连接

Question

我想在SQL Server的SELECT语句之间设置CONCAT。

所有列都是布尔值

如果所有列都为真，则输出如下所示

GGPNES

我试着使用这个，它不工作

DECLARE @concat VARCHAR(40) ='';
SELECT
Smoke, Invoice,
Party, Summary,
MySelf, Export,
CASE
WHEN MyTable.Smoke = 1 OR MyTable.Invoice = 1 
THEN @concat + 'GG' 
WHEN MyTable.Party = 1 
THEN @concat + 'P'
WHEN MyTable.Summary = 1 
THEN @concat + 'N'
WHEN MyTable.MySelf = 1 
THEN @concat + 'E'
WHEN MyTable.Export = 1 
THEN @concat + 'S'
END
FROM
MyTable

Answer 1

我认为你想要一个CASE表达式的连接:

CASE WHEN Smoke = 1 OR Invoice = 1 THEN 'GG' ELSE '' END +
CASE WHEN Party = 1 THEN 'P' ELSE '' END +
CASE WHEN Summary = 1 THEN 'N' ELSE '' END +
CASE WHEN MySelf = 1 THEN 'E' ELSE '' END +
CASE WHEN Export = 1 THEN 'S' ELSE '' END