一、创建表

create table stuUnion

(

 sid int identity primary key,

 cid int,

 id varchar(500)

)

二、添加数据

insert into stuUnion

select 1,'a' union

select 1,'b' union

select 2,'c' union

select 2,'d' union

select 3,'e' union

select 3,'f' union

select 3,'g'

三、用标量函数查询

(1)创建标量函数

create function b(@cid int)

returns varchar(500)

as

begin

declare @s varchar(500)

select @s=isnull(@s+'','')+rtrim(id)+',' from stuUnion where cid=@cid

return @s

end;

(2)用标量函数查询

select cid,dbo.b(cid) as id from stuUnion group by cid

三、用sqlserver的xml

select cid,ID=STUFF((select ' '+rtrim(id)+',' from stuUnion where st.cid=cid order by id for XML path('')),1,1,'') from stuUnion st group by cid