Comma separated Days names from Column

Question

I have table with below structure

I was trying to get the result like

Please guide me how can I achieve this result with the query. I tried with Case Expression but it won't concatenate the string.

CASE WHEN EveryDay=1 THEN @Days ='Every Day' WHEN SUN=1 THEN @Days = 'SUNDAY'

Realised to OP is trying to convert columns to delimited, not rows. Can't actually find a dup for that (although I am sure they are out there). — Thom A, Oct 09 '19 at 09:33

score 4 · Accepted Answer · answered Oct 09 '19 at 09:32

This should get you ball rolling for you:

SELECT CASE EveryDay WHEN 1 THEN 'Everyday'
                     ELSE STUFF(CONCAT(',' + CASE SUN WHEN 1 THEN 'SUN' END,
                                       ',' + CASE MON WHEN 1 THEN 'MON' END,
                                       ...
                                       ',' + CASE SAT WHEN 1 THEN 'SAT' END),1,1,'')
       END
FROM ...

score 2 · Answer 2 · answered Oct 09 '19 at 09:52

Larnu already gives you a good answer, but in case of SendSms = 0 it 'ill returns EveryDay. Thus I'm posting this answer to cover it

SELECT *,
IIF(SendSms = 1, 
    IIF(EveryDay = 1, 'EveryDay',
        STUFF(CONCAT(CASE WHEN Sun = 1 THEN ',Sunday' END,
                     CASE WHEN Mon = 1 THEN ',Monday' END,
                     CASE WHEN Tue = 1 THEN ',Tuesday' END,
                     CASE WHEN Wed = 1 THEN ',Wednesday' END,
                     CASE WHEN Thu = 1 THEN ',Thuresday' END,
                     CASE WHEN Fri = 1 THEN ',Friday' END,
                     CASE WHEN Sat = 1 THEN ',Saturday' END
                    ), 1, 1, ''
             )
        )
    , 'No SMS') Days
FROM
(
  VALUES
  (1, 1, 0, 1, 0, 0, 1, 0, 0, 0),
  (2, 1, 1, 0, 0, 0, 0, 0, 0, 0),
  (3, 0, 1, 0, 0, 0, 0, 0, 0, 1)
) T(ID, SendSms, EveryDay, Sun, Mon, Tue, Wed, Thu, Fri, Sat)

Online Demo

score 2 · Answer 3 · answered Oct 09 '19 at 10:22

In SQL Server 2017 you can use CONCAT_WS function to make it slightly easier:

SELECT CASE WHEN EveryDay = 1 THEN 'EveryDay'
            ELSE CONCAT_WS(
                     ',', 
                     CASE WHEN SUN = 1 THEN 'Sunday'    END, 
                     CASE WHEN MON = 1 THEN 'Monday'    END, 
                     CASE WHEN TUE = 1 THEN 'Tuesday'   END, 
                     CASE WHEN WED = 1 THEN 'Wednesday' END, 
                     CASE WHEN THU = 1 THEN 'Thursday'  END, 
                     CASE WHEN FRI = 1 THEN 'Friday'    END, 
                     CASE WHEN SAT = 1 THEN 'Saturday'  END
                 )
       END AS Days

I forgot they'd introduced that with 2017. Thanks for the reminder. — Thom A, Oct 09 '19 at 10:26

Comma separated Days names from Column

3 Answers3