SQL逗号分隔的多列列表 [英] SQL Comma Separated List from Multiple Columns

问题描述

我有这样的数据:

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

对于每个 customer_id，我想要一个逗号分隔的列表，指示客户运营的月份:

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

等等.我如何使用 SQL Server 2005 SQL(而非 T-SQL)轻松生成此逗号分隔列表?

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

我在 Stack Overflow 和其他地方看到的大多数解决方案似乎都是基于连接多行值而不是列值来创建逗号分隔的列表:

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
• 对于 XML 路径('')
• 关联子查询结合 REPLACE/STUFF/SUBSTRING

我是否遗漏了一些明显的东西?在此先感谢您的帮助或在此处指向适当的现有解决方案.

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

推荐答案

你可以这样做.

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

这篇关于SQL逗号分隔的多列列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！