Concatenation by using CASE statement-CodePudding

I want to CONCAT between this on my SELECT statement in SQL Server.

All the columns are booleans.

The output will be like this if all of columns that are true

GGPNES

I tried to use this and it doesn't work

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

CodePudding user response：

I think that you want a concatenation of CASE expressions:

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