应该是老问题了，求SQL语句：查询一列的多条记录
应该是老问题了，表Sort的结构如下：
          sort_id           sort_name                   parent_id
1 冶金 0
2 焊接某某人 1
3 初级工 2
4 机械 0
5 机械加工 4
6 铣工 5
7 初级工 6
8 初级职业功能 7
9 电弧焊 1
10 初级工 9

sort_id是主键，parent_id是上一级sort_id，现在我要得到的就是每一个sort的路径，结果应该如下：

                sort_id       path
1 冶金
2 冶金.焊接某某人
3 冶金.焊接某某人.初级工
4 机械
5 机械.机械加工
5 机械.机械加工.铣工
6 机械.机械加工.铣工.初级工
                ....................


也就是说，每个path的值实际上为他的parent的path加上他自己的名字,在线等待，谢谢！！

------解决方案--------------------
剛貼了個相似的,LZ自己改下吧
create table tree(id int,pid int ,name varchar(02))
insert into tree
select 1,0, 'A ' union all
select 2,1, 'B ' union all
select 3,4, 'C ' union all
select 4,7, 'D ' union all
select 5,2, 'E ' union all
select 6,4, 'F ' union all
select 7,2, 'G ' union all
select 8,5, 'H ' union all
select 9,0, 'I ' union all
select 10,8, 'J '

GO
create procedure dbo.usp_test
@pid int
AS

begin
set nocount on
declare @level int ,@i int ,@flag int
declare @stack table (pid int,id int, level int,row int , flag int,name varchar(02))
/*將開始層插入 */
insert into @stack(pid,id,level,row)
select @pid,@pid,0,0
select @level = 1,@i=1,@flag=1
insert @stack
select pid,id, @level,0,1,name
from tree(nolock)
where pid = @pid and id is not null

while @level > 0
begin
if exists (select * from @stack where level = @level and flag=1)
begin
select @pid = min(id)
from @stack
where level = @level and flag=1
update @stack set flag =0 , row=@i
where level = @level
and id = @pid and flag =1
set @i = @i +1
insert @stack
select pid,id, @level +1,0,1 ,name
from tree(nolock)
where pid = @pid and id is not null
if @@rowcount > 0
select @level = @level + 1
end
else
begin
select @level = @level - 1
end
end

declare @str varchar(50)
set @str= ' '
select @str=@str+ '- '+name from @stack where name is not null order by row
select stuff(@str,1,1, ' ')
set nocount off
end

GO
--

--exec usp_test 0


drop table tree
drop proc usp_test
------解决方案--------------------
--建立函數
Create Function F_GetSort(@sort_id Int)
Returns Nvarchar(2000)
As
Begin
Declare @S Nvarchar(2000)
Select @