SQL Comma Separated List from Multiple Columns

Question

Hi,

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

Assuming your table has 13 columns (1 for each month of the year + CUSTOMER_ID), you can write something like:

SELECT 
  CUSTOMER_ID,
  CASE OPERDAYSJUL > 0 THEN 'Jul,' ELSE '' END +
  CASE OPERDAYSAUG > 0 THEN 'Aug,' ELSE '' END +
  ...
FROM MyTable

and build up a string that represents your comma-separated list using CASE statements, one for each month.

Answer 2

You can do something like this.

CONCAT(CASE OPERDAYSJUL > 0 THEN "Jul," ELSE "" END,CASE OPERDAYSAUG > 0 THEN "Aug" ELSE "" END ... )

Answer 3

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;

Answer 4

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 5

select 
   customer_id,
   case when len(operating_month) > 0 then 
       left(operating_month, len(operating_month) - 1)
   else 
       operating_month 
   end as operating_month
from
(
   SELECT CUSTOMER_ID,  
   CASE OPERDAYSJUL > 0 THEN 'Jul,' ELSE '' END 
   +  CASE OPERDAYSAUG > 0 THEN 'Aug,' ELSE '' END 
   +  ... 
   as operating_month
   FROM MyTable
) as x