declare @tb table (id int, name varchar(10)) insert into @tb values(1, 'a') insert into @tb values(1, 'b') insert into @tb values(2, 'a') insert into @tb values(2, 'c') select id , [ccname]= stuff((select ','+[name] from @tb t where id =tv.id for xml path('')), 1, 1, '') from @tb as tv group by id /* id ccname 1 a,b 2 a,c */
sql server 2000 CREATE FUNCTION fn_str(@id) RETURNS varchar(1000) begin DECLARE @str VARCHAR(1000) SET @str='' SELECT @str=@str+','+name FROM table WHERE id=@id RETURN STUFF(@str,1,1,'') END
SELECT id,name=fn_str(id) FROM table /*id name ----------- ---------------- 1 a,b 2 c,d */
insert into @tb values(1, 'a')
insert into @tb values(1, 'b')
insert into @tb values(2, 'a')
insert into @tb values(2, 'c') select id , [ccname]=
stuff((select ','+[name] from @tb t where id =tv.id for xml path('')), 1, 1, '')
from @tb as tv
group by id
/*
id ccname
1 a,b
2 a,c
*/
sql server 2000
CREATE FUNCTION fn_str(@id)
RETURNS varchar(1000)
begin
DECLARE @str VARCHAR(1000)
SET @str=''
SELECT @str=@str+','+name FROM table WHERE id=@id
RETURN STUFF(@str,1,1,'')
END
SELECT id,name=fn_str(id) FROM table
/*id name
----------- ----------------
1 a,b
2 c,d
*/