SQL逗号分隔的多个列列表
我有一个看起来像这样的数据:SQL逗号分隔的多个列列表
CUSTOMER_ID OPERDAYSJUL OPERDAYSAUG OPERDAYSSEP ... OPERDAYSJUN
1 30 15 2
2 5 1 0
3 6 0 12
4 12 5 23
对于每个CUSTOMER_ID,我想说明一个逗号分隔的列表,它月份客户操作:
CUSTOMER_ID OPERATING_MONTHS
1 Jul, Aug, Sep
2 Jul, Aug
3 Jul, Sep
4 Jul, Aug, Sep
等等。我如何使用SQL Server 2005 SQL(而不是T-SQL)轻松生成这个以逗号分隔的列表?
大多数解决方案我在这里看到的堆栈溢出和其他地方似乎基于加入多行值,而不是列值创建逗号分隔的列表:
- T-SQL
- FOR XML PATH(“” )
- 相关子查询加上REPLACE/STUFF/SUBSTRING
我失去了一些东西明显?在此先感谢您的帮助或指向适当的现有解决方案。
你可以做这样的事情。
CONCAT(CASE OPERDAYSJUL > 0 THEN "Jul," ELSE "" END,CASE OPERDAYSAUG > 0 THEN "Aug" ELSE "" END ...)
假设你的表有13列(1年+ CUSTOMER_ID每月),你可以写这样的:
SELECT
CUSTOMER_ID,
CASE OPERDAYSJUL > 0 THEN 'Jul,' ELSE '' END +
CASE OPERDAYSAUG > 0 THEN 'Aug,' ELSE '' END +
...
FROM MyTable
,并建立代表使用CASE语句以逗号分隔的列表的字符串,每个月一个。
我也这么想过,但是如何摆脱尾随的逗号? – 2009-10-21 20:18:41
谢谢pmarflee;你如何从最后一个值中删除逗号? – iokevins 2009-10-21 20:19:58
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;
这剥去多余的逗号
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
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
你需要格式化这个 – 2009-10-21 20:31:27
你为什么说 “没有T-SQL”? T-SQL只是SQL Server使用的SQL方言的名称。 http://en.wikipedia.org/wiki/Microsoft_SQL_Server#T-SQL – LukeH 2009-10-21 21:05:44
感谢您对卢克的洞察力;我似乎错误地使用了这个术语。我想限制答案为简单的SELECT查询语法,不受本地变量,临时表,循环等影响。再次感谢。 – iokevins 2009-10-21 21:05:45