How to get output to not write more SQL CASES using select query-CodePudding

I have below xyz table data

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

I want output like below

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

I tried by using switch case but need to write more cases

any other ways to get output

CodePudding user response：

The CONCAT_WS() function is your friend here:

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;

CodePudding user response：

Just another option using a bit of JSON

Select A.ID
      ,B.Divisions
 From  YourTable A
 Cross Apply (
                Select Divisions = string_agg(Left([Key],1),'-')
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper)) 
                 WHere [Key] not in ('id')
                   and Value = 1
             ) B

Results

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

Just a note:

Rather than Left([Key],1) you could have replace([Key],'Division','') if the division name is more than 1 character.