如何查询这样的语句
id,name,pid
1,a,
2,a1,1
3,a2,1
4,a3,1
5,b,
6,b1,5
7,b2,5
8,c,
9,d,
得到
a,a1,a2,a3
b,b1,b2
c,
d

------解决方案--------------------
create table tb(id int,name varchar(10),pid int)

insert into tb values(1, 'a ',null)
insert into tb values(2, 'a1 ',1)
insert into tb values(3, 'a2 ',1)
insert into tb values(4, 'a3 ',1)
insert into tb values(5, 'b ',null)
insert into tb values(6, 'b1 ',5)
insert into tb values(7, 'b2 ',5)
insert into tb values(8, 'c ',null)
insert into tb values(9, 'd ',null)
go
select * into temp from
(
select id , name from tb where pid is null
union all
select pid id , name from tb where pid is not null
) t
order by id ,name
go
--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(name as varchar) from temp where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go

--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as name from temp

drop table tb,temp

/*
id name
----------- ----------
1 a,a1,a2,a3
5 b,b1,b2
8 c
9 d

（所影响的行数为 4 行）
*/


------解决方案--------------------
参考：

--生成测试数据
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1, '张三 '
insert into 表 select 1, '李四 '
insert into 表 select 1, '王五 '
insert into 表 select 2, '赵六 '
insert into 表 select 2, '邓七 '
insert into 表 select 2, '刘八 '
go

--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ' '
select @ret = @ret+ ', '+人员 from 表 where 部门 = @department
set @ret = stuff(@ret,1,1, ' ')
return @ret
end
go


--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go

--输出结果
/*
部门 人员
---- --------------
1 张三,李四,王五
2 赵六,邓七,刘八
*/


--删除测试数据
drop function f_str
drop table 表
go
------解决方案--------------------
这个不需要临时表.

create table tb(id int,name varchar(10),pid int)

insert into tb values(1, 'a ',null)
insert into tb values(2, 'a1 ',1)
insert into tb values(3, 'a2 ',1)
insert into tb values(4, 'a3 ',1)
insert into tb values(5, 'b ',null)
insert into tb values(6, 'b1 ',5)
insert into tb values(7, 'b2 ',5)
insert into tb values(8, 'c ',null)
insert into tb values(9, 'd ',null)
go
select * from
(
select id , name from tb where pid is null
union all
select pid id , name from tb where pid is not null
) t

go
--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(name as varchar) from
(
select id , name from tb where pid is null
union all
select