查询语句帮忙
id   name         parentid     code
0     a               root             0100
1     a1             0                   0101
2     a2             0                   0102
3     b               root             0200
4     b1             3                   0201
如何变成
a   ,     a1   ,   a2  
b   ,     b1   ,        



------解决方案--------------------
用函数

------解决方案--------------------
Create Function F_GetChildren(@id Varchar(10))
ReturnS Varchar(8000)
AS
Begin
Declare @S Varchar(8000)
Select @S = name From TEST Where id = @id
Select @S = @S + ', ' + name From TEST Where parentid = @id
Return @S
End
GO
Select dbo.F_GetChildren(id) As name From TEST Where parentid = 'root '
Go
------解决方案--------------------
create function fn_tree(
@id varchar(20))
returns varchar(400)
as
begin
declare @r varchar(400)
select @r=name from tablename where id=@id
if exists (select 1 from tablename where parentid=@id)
select @r=@r+ ', '+dbo.fn_tree(id) from tablename where parentid=@id order by id
return @r
end

go

--调用
select dbo.fn_tree(id) as Tree
from tablename
where parentid= 'root '
------解决方案--------------------
--創建測試環境
Create Table TEST
(id Int,
name Varchar(10),
parentid Varchar(10),
code Varchar(20))
Insert TEST Select 0, 'a ', 'root ', '0100 '
Union All Select 1, 'a1 ', '0 ', '0101 '
Union All Select 2, 'a2 ', '0 ', '0102 '
Union All Select 3, 'b ', 'root ', '0200 '
Union All Select 4, 'b1 ', '3 ', '0201 '
GO

-- 查询处理
DECLARE
@s nvarchar(4000), @i int
SELECT TOP 1
@s = ' ',
@i = COUNT(*)
FROM TEST
WHERE parentid <> 'root '
GROUP BY parentid
ORDER BY COUNT(*) DESC

WHILE @i > 0
SELECT
@s = N ',
' + QUOTENAME( 'col ' + RTRIM(@i + 1))
+ N '=MAX(CASE rid WHEN ' + RTRIM(@i)
+ N ' THEN name ELSE N ' ' ' ' END) '
+ @s,
@i = @i - 1

EXEC(N '
SELECT
col1 = MAX(CASE rid WHEN 0 THEN name END)
' + @s + N '
FROM(
SELECT
parentid = id, name,
rid = 0
FROM TEST
WHERE parentid = ' 'root ' '
UNION ALL
SELECT
parentid, name,
rid = (SELECT COUNT(*) FROM TEST WHERE parentid = A.parentid AND id <= A.id)
FROM TEST A
WHERE parentid <> ' 'root ' '
)A
GROUP BY parentid
')
GO

DROP TABLE TEST