IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))

DROP TABLE [dbo].[test]

GO

CREATE TABLE [dbo].[test](

[id] [int] NULL,

[name] [nvarchar](50) NULL,

[upid] [int] NULL

) ON [PRIMARY]

GO

insert into test select 1,'A',0

insert into test select 2,'B',1

insert into test select 3,'C',2

insert into test select 4,'D',1

insert into test select 5,'E',4

insert into test select 6,'F',3

insert into test select 7,'G',5

insert into test select 8,'H',6

--创建用户定义函数,每个子节点de父节点的信息

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[f_getParent]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[f_getParent]

GO

create function f_getParent(@ID int)

returns varchar(40)

as

begin

declare @ret varchar(40)

declare @str nvarchar(100)

while exists(select 1 from test where ID=@ID and upid<>0 and upid<>1)

begin

select @ID=b.ID--,@ret=','+rtrim(b.ID)+isnull(@ret,'')

,@ret=','+rtrim(b.name)+isnull(@ret,'')

from

test a,test b

where

a.ID=@ID and b.ID=a.upid

end

set @ret=stuff(@ret,1,1,'')

return @ret

end

go

--执行查询

select ID,isnull(dbo.f_getParent(ID),'') as parentID from test

go

 

查询结果：

ID parentID

1

2

3     B

4

5     D

6     B,C

7     D,E

8     B,C,F