--建立測試環境
Create Table TEST(P Varchar(10), C Varchar(10))
Insert Into TEST
Select 'A', 'A1'
Union Select 'A', 'A2'
Union Select 'A1', 'A11'
Union Select 'A1', 'A12'
Union Select 'A11', 'D'
Union Select 'A2', 'A21'
Union Select 'A2', 'A22'
Union Select 'A21', 'F'
GO
--建立函數
Create Function F_GetPath(@C Varchar(10))
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @C = P, @S = C From TEST Where C = @C
While @@ROWCOUNT > 0
Select @C = P, @S = C + '-' + @S From TEST Where C = @C
Return @S
End
GO
--測試
Select * From TEST Order By dbo.F_GetPath(C)
GO
--刪除測試環境
Drop Table TEST
Drop Function F_GetPath
--結果
/*
P C
A A1
A1 A11
A11 D
A1 A12
A A2
A2 A21
A21 F
A2 A22
*/
--建立函數
Create Function F_GetPath(@C Varchar(10))
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @C = P, @S = C From TEST Where C = @C
While @@ROWCOUNT > 0
Select @C = P, @S = C + '-' + @S From TEST Where C = @C
Return @S
End
GO
--測試
Select * From TEST Order By dbo.F_GetPath(C)
Create table table1(p nvarchar(10),C nvarchar(10))
insert into table1
select 'A', 'A1'
union select 'A', 'A2'
union select 'A1', 'A11'
union select 'A1', 'A12'
union select 'A11', 'D'
union select 'A2', 'A21'
union select 'A2', 'A22'
union select 'A21', 'F'