if object_id('tb') is not null drop table tbCREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3create function dbo.getstr(@str varchar(20))
returns varchar(20)
as
begin
declare @sql varchar(50)
set @sql=''
select @sql=@sql+','+ltrim(col2) from tb where col1=@str
return stuff(@sql,1,1,'')
endselect distinct col1,dbo.getstr(col1) col2 from tbcol1 col2
---------- --------------------
a 1,2
b 1,2,3(2 行受影响)
returns varchar(20)
as
begin
declare @sql varchar(50)
set @sql=''
select @sql=@sql+','+ltrim(col2) from tb where col1=@str
return stuff(@sql,1,1,'')
endselect distinct col1,dbo.getstr(col1) col2 from tbcol1 col2
---------- --------------------
a 1,2
b 1,2,3(2 行受影响)
INSERT # SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3;with t1 as
(
select id=row_number()over(partition by col1 order by getdate()), col1, convert(varchar(max),col2)col2 from #
),
t2 as
(
select * from t1 where id = 1
union all
select b.id, a.col1, convert(varchar(max),a.col2+','+b.col2) from t2 a join t1 b on a.col1=b.col1 and a.id=b.id-1
)
select col1,col2 from t2 t where not exists (select 1 from t2 where col1=t.col1 and id>t.id)
INSERT # SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3select col1, col2 = stuff((select ',' + ltrim(col2) from # where col1 = t.col1 for xml path('')) , 1, 1, '') from # as t group by col1