BOM查找指定子项的所有父项,并以字段的形式输出

/*---------------------------------

-- Author : htl258(Tony)

-- Date : 2009-09-16 18:08:58

-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

Mar 29 2009 10:27:29

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/

--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL

DROP TABLE [tb]

GO

CREATE TABLE [tb]([ID] INT,[NAME] NVARCHAR(10),[PARENTID] INT)

INSERT [tb]

SELECT 1,'A',0 UNION ALL

SELECT 2,'A1',1 UNION ALL

SELECT 3,'A2',1 UNION ALL

SELECT 4,'B',0 UNION ALL

SELECT 5,'B1',4 UNION ALL

SELECT 6,'A11',2 UNION ALL

SELECT 7,'A111',6 UNION ALL

SELECT 8,'B11',5 UNION ALL

SELECT 9,'A1111',7 UNION ALL

SELECT 10,'A11111',9

GO

--SELECT * FROM [tb]

-->SQL查询如下:

IF OBJECT_ID('FN_STR') IS NOT NULL

DROP FUNCTION FN_STR

GO

CREATE FUNCTION FN_STR(@id INT)

RETURNS NVARCHAR(100)

AS

BEGIN

DECLARE @s NVARCHAR(100);

WHILE EXISTS(SELECT 1 FROM tb WHERE id=@id)

BEGIN

SELECT @s=ISNULL(@s+',','')+NAME FROM tb WHERE id=@id

SET @id=(SELECT PARENTID FROM tb WHERE id=@id)

END

RETURN STUFF(@s,1,CHARINDEX(',',@s),'')

END

GO

SELECT dbo.FN_STR(9) AS path

/*

path

-------------------------------

A111,A11,A1,A

(1 行受影响)

*/