CREATE TABLE tb(dah VARCHAR(10),fid int) INSERT tb SELECT 'A' dah,10 fid UNION SELECT 'A',11 UNION SELECT 'A',12 UNION SELECT 'B',20 UNION SELECT 'B',21 UNION SELECT 'B',22 UNION SELECT 'C',30 UNION SELECT 'C',31SELECT dah,fid=STUFF((SELECT ','+CAST(fid AS VARCHAR(10)) FROM tb WHERE dah=t.dah FOR XML PATH('')),1,1,'') FROM TB t GROUP BY dah
WITH CTE AS (SELECT 'A' dah,10 fid UNION SELECT 'A',11 UNION SELECT 'A',12 UNION SELECT 'B',20 UNION SELECT 'B',21 UNION SELECT 'B',22 UNION SELECT 'C',30 UNION SELECT 'C',31 ) SELECT dah,fid=STUFF((SELECT ','+CAST(fid AS VARCHAR(10)) FROM CTE WHERE dah=t.dah FOR XML PATH('')),1,1,'') FROM CTE t GROUP BY t.dah
create table tb1(dah varchar(10),fid int)insert into tb1 select 'A',10 union all select 'A',11 union all select 'A',12 union all select 'B',20 union all select 'B',21 union all select 'B',22 union all select 'C',30 union all select 'C',31create function joinstr (@dah varchar(10)) returns varchar(1000) as begin declare @sql varchar(100) set @sql='' select @sql=@sql+','+convert(varchar(10),fid) from tb1 where dah=@dah return stuff(@sql,1,1,'')end select dah,dbo.joinstr(dah) fid from tb1 group by dah
select a.dah,STUFF((select ','+CONVERT(varchar(10),fid) from tb1 where dah=a.dah for xml path('')) ,1,1,'') from tb1 a group by a.dah
declare @tab table(id varchar(10),val int) insert into @tab select 'A', 10 union select 'A' , 11 union select 'A' , 12 union select 'B' , 20 union select 'B' , 21 union select 'B' , 22 union select 'C' , 30 union select 'C' , 31 select id,val=stuff((select ','+cast(val as varchar) from @tab where id=t.id for xml path('')),1,1,'') from @tab t group by id
INSERT tb
SELECT
'A' dah,10 fid UNION SELECT
'A',11 UNION SELECT
'A',12 UNION SELECT
'B',20 UNION SELECT
'B',21 UNION SELECT
'B',22 UNION SELECT
'C',30 UNION SELECT
'C',31SELECT dah,fid=STUFF((SELECT ','+CAST(fid AS VARCHAR(10)) FROM tb WHERE dah=t.dah FOR XML PATH('')),1,1,'')
FROM TB t
GROUP BY dah
'A' dah,10 fid UNION SELECT
'A',11 UNION SELECT
'A',12 UNION SELECT
'B',20 UNION SELECT
'B',21 UNION SELECT
'B',22 UNION SELECT
'C',30 UNION SELECT
'C',31
)
SELECT dah,fid=STUFF((SELECT ','+CAST(fid AS VARCHAR(10)) FROM CTE WHERE dah=t.dah FOR XML PATH('')),1,1,'')
FROM CTE t
GROUP BY t.dah
create table tb1(dah varchar(10),fid int)insert into tb1 select 'A',10
union all
select 'A',11
union all
select 'A',12
union all
select 'B',20
union all
select 'B',21
union all
select 'B',22
union all
select 'C',30
union all
select 'C',31create function joinstr (@dah varchar(10)) returns varchar(1000)
as
begin declare @sql varchar(100)
set @sql=''
select @sql=@sql+','+convert(varchar(10),fid) from tb1 where dah=@dah
return stuff(@sql,1,1,'')end
select dah,dbo.joinstr(dah) fid from tb1 group by dah
select a.dah,STUFF((select ','+CONVERT(varchar(10),fid) from tb1 where dah=a.dah for xml path('')) ,1,1,'')
from tb1 a group by a.dah
declare @tab table(id varchar(10),val int)
insert into @tab
select 'A', 10 union
select 'A' , 11 union
select 'A' , 12 union
select 'B' , 20 union
select 'B' , 21 union
select 'B' , 22 union
select 'C' , 30 union
select 'C' , 31
select id,val=stuff((select ','+cast(val as varchar) from @tab where id=t.id for xml path('')),1,1,'')
from @tab t group by id