BOM(父项子项)节点展开

数据表：

id          parentId    sortName
----------- ----------- ----------
1           0           A
2           0           B
3           1           AA
4           3           AAA
5           4           AAAA
6           2           BB

能不能通过SQl直接得到这样的结果

id  Sort_Description
1        A
2        B
3        A-AA
4        A-AA-AAA
5        A-AA-AAA-AAAA
6        B-BB

 

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

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

    DROP TABLE [tb]

GO

CREATE TABLE tb

(

    id INT, parentId INT,

    sortName VARCHAR(10)

)

INSERT INTO tb

SELECT 1, '0', 'A' UNION ALL

SELECT 2, '0', 'B' UNION ALL

SELECT 3, '1', 'AA' UNION ALL

SELECT 4, '3', 'AAA' UNION ALL

SELECT 5, '4', 'AAAA' UNION ALL

SELECT 6, '2', 'BB'

GO

 

--> SQL查询如下：

IF OBJECT_ID('dbo.f_str')>0

    DROP FUNCTION dbo.f_str

GO

 

CREATE FUNCTION dbo.f_str

(

    @id INT

)

RETURNS VARCHAR(1000)

AS

BEGIN

    DECLARE @parentId INT, @s VARCHAR(1000)

    WHILE EXISTS(

              SELECT 1

              FROM tb

              WHERE id = @id

          )

    BEGIN

        SELECT @s = sortname+ISNULL('-'+@s, ''),

              @parentId = parentid

        FROM tb

        WHERE id = @id

        SET @id = @parentId

    END

    RETURN @s

END

GO

 

SELECT *, dbo.f_str(id) Sort_Description

FROM tb

/*

id          parentId    sortName   Sort_Description

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

1           0           A          A

2           0           B          B

3           1           AA         A-AA

4           3           AAA        A-AA-AAA

5           4           AAAA       A-AA-AAA-AAAA

6           2           BB         B-BB

 

(6 行受影响)

*/