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 行受影响)

*/