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 ,
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 ,
调试欢乐多
假設是這樣的數據,你的結果是怎樣的?
id name parentid code
0 a root 0100
1 a1 0 0101
2 a2 0 0102
3 b root 0200
4 b1 3 0201
5 a11 1 010101
6 a12 1 010102
7 a21 2 010201
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
@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
endgo--调用
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
--創建函數
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 Order By id
Return @S
End
GO
--測試
Select
dbo.F_GetChildren(id) As name
From
TEST
Where parentid = 'root'
GO
--刪除測試環境
Drop Table TEST
Drop Function F_GetChildren
--結果
/*
name
a, a1, a2
b, b1
*/
col1 ,col2, col3
a a1 a2
b b1 ''
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(*) DESCWHILE @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 - 1EXEC(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
')
GODROP TABLE TEST-- 结果:
col1 col2 col3
---------- ---------- ----------
a a1 a2
b b1