CREATE TABLE #a (
[C1] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL
) insert into #a values ('95533')
insert into #a values('95566')
insert into #a values('95588')
insert into #a values('95599')
insert into #a values('95533')
insert into #a values('95577')可不可以用一条语句select出下面的结果:
'95533,95566,95588,95599,95533,95577'
select @s=isnull(@s,'')+[C1] from #a
select @s
select @s=isnull(@s,'')+','+[C1] from #a
select @s
declare @sql varchar(8000)
select @sql =isnull(@sql +',','')+C1 from #a
select @sql
declare @s varchar(8000)
select @s=isnull(@s,'''')+[C1]+',' from #a
select LEFT(@s,LEN(@s)-1)+''''/*------------------------------------------------------------------------------------------------------------------------------------------------
'95533,95566,95588,95599,95533,95577,95533,95566,95588,95599,95533,95577'(1 row(s) affected)
*/
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT
[values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
--
1 aa,bb
2 aaa,bbb,ccc
--