SQL Comma Separated List from Multiple Columns

Question

I have data that looks like this:

CUSTOMER_ID  OPERDAYSJUL  OPERDAYSAUG  OPERDAYSSEP ... OPERDAYSJUN
1            30           15           2
2            5            1            0
3            6            0            12
4            12           5            23

For each customer_id, I want a comma-delimited list indicating which months the customer operates:

CUSTOMER_ID  OPERATING_MONTHS
1            Jul, Aug, Sep
2            Jul, Aug
3            Jul, Sep
4            Jul, Aug, Sep

and so forth. How might I use SQL Server 2005 SQL (not T-SQL) to easily produce this comma-delimited list?

Most solutions I see here on Stack Overflow and elsewhere seem to create comma-separated lists based on joining multiple rows values, not column values:

• T-SQL
• FOR XML PATH('')
• Correlated subquery combined with REPLACE/STUFF/SUBSTRING

Am I missing something obvious? Thanks in advance for assistance or pointer to appropriate existing solution here.

Answer 1

This strips off the extra comma

SELECT 
  CUSTOMER_ID,
  SUBSTRING(
    CASE WHEN OPERDAYSJUL > 0 THEN ', Jul' ELSE '' END +
    CASE WHEN OPERDAYSAUG > 0 THEN ', Aug' ELSE '' END +
    ...
    CASE WHEN OPERDAYSJUN > 0 THEN ', Jun' ELSE '' END,
   3, 255)
FROM TheTable

Answer 2

declare @t table (CUSTOMER_ID int
  , OPERDAYSJUL int
  , OPERDAYSAUG int
  , OPERDAYSSEP int
  -- ... rest of 9 months here
  );

insert into @t (CUSTOMER_ID, OPERDAYSJUL, OPERDAYSAUG, OPERDAYSSEP)
select 1, 30, 15, 22 union all
select 2, 0, 10, 10 union all
select 3, 0, 0, 10 union all
select 4, 0, 0, 0 union all
select 5, 10, 0, 10 union all
select 6, 10, 10, 0 union all
select 7, 0, 10, 0 union all
select 8, 10, 0, 0;    

with cte_months as (
select CUSTOMER_ID
  , case when OPERDAYSJUL=0 then '' else ', Jul' end 
  + case when OPERDAYSAUG=0 then '' else ', Aug' end
  + case when OPERDAYSSEP=0 then '' else ', Sep' end
  -- ... rest of 9 months here
  as month_list
 from @t)
 select CUSTOMER_ID, substring(month_list, 3, 70) 
 from cte_months;