I am Berke

Nem is olyan régen jött egy olyan feladat, hogy egy "kilapított" hierarchiából kellene "rendes" hierarchiát csinálni SQL-ben. Ez több dolog miatt is érdekes volt:

• több root volt az adathalmazban (no comment:)),
• nem minden szint volt megadva, csak amelyikhez tartozott még valami plusz adat.

Innentől kezdve már nem is olyan egyszerű, de megoldható. Az alábbi minta kód bemutatja lépésről lépésre, hogyan lehet felépíteni ezt a fastruktúrát, illetve a végeredményben a hiányzó szintek is megjelennek. (az egyszerűség kedvéért tekintsünk el attól, hogy több root van, ami ugye elvileg nem lehetne): 

/***********************************************
This code is provided AS IS, without warranty!!!
***********************************************/

USE tempdb;
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE [OBJECT_ID] = OBJECT_ID('[dbo].[FlatHierarchy]'))
    DROP TABLE [dbo].[FlatHierarchy];
GO

--"kilapított" hierarchia adatok tárolására szolgáló tábla
CREATE TABLE [dbo].[FlatHierarchy]
(
    [Level1]    varchar(100) NOT NULL,
    [Level2]    varchar(100) NULL,
    [Level3]    varchar(100) NULL,
    [Level4]    varchar(100) NULL,
    [Level5]    varchar(100) NULL,
    [LevelData] varchar(100) NULL
);
GO

--minta adatok (SQL 2008 alatt csak, korábbi verzióknál a kommentelt részt kell használni)
INSERT INTO [dbo].[FlatHierarchy]  ([Level1], [Level2], [Level3], [Level4], [Level5], [LevelData])
VALUES
('Level1', NULL, NULL, NULL, NULL, 1),
('Level1', 'Level2', NULL, NULL, NULL, 2),
('LevelA', 'LevelTwo', NULL, NULL, NULL, 2),
('Level1', 'Level2', NULL, NULL, NULL, 3),
('Level1', 'Level2', 'Level3', 'Level4', NULL, 4),
('Level1', 'Level2', 'Level3', 'Level4', 'Level5', 4);
/*--SQL 2005 vagy korábbi*/
--INSERT INTO [dbo].[FlatHierarchy]  ([Level0], [Level1], [Level2], [Level3], [Level4], [Level5], [LevelData])
--SELECT 'Level1', NULL, NULL, NULL, NULL, 1
--UNION
--SELECT 'Level1', 'Level2', NULL, NULL, NULL, 2
--UNION
--SELECT 'LevelA', 'LevelTwo', NULL, NULL, NULL, 2
--UNION
--SELECT 'Level1', 'Level2', NULL, NULL, NULL, 3
--UNION
--SELECT 'Level1', 'Level2', 'Level3', 'Level4', NULL, 4
--UNION
--SELECT 'Level1', 'Level2', 'Level3', 'Level4', 'Level5', 4
GO

--adatok visszaellenőrzése
SELECT * FROM [dbo].[FlatHierarchy];
GO

 
--hierarchia felépítése

IF EXISTS (SELECT 1 FROM sys.objects WHERE [OBJECT_ID] = OBJECT_ID('#H'))
    DROP TABLE #H;
GO

CREATE TABLE #H 

(
    [ID]        int identity NOT NULL,
    [ParentID]  int NULL,
    [Level]     int NOT NULL,
    [LevelName] varchar(100) NOT NULL
);
GO

DECLARE @lvl    int;
DECLARE @stmt   nvarchar(max);
SET @lvl = 1; 

--Level 1 felépítése
INSERT INTO #H
SELECT
    NULL,
    1,
    [Level1] AS [LevelName]
FROM
    [dbo].[FlatHierarchy]
GROUP BY
    [Level1];
 
--Level2 - Level5 felépítése
WHILE @lvl < 5
    BEGIN
        SET @stmt = N'
        ;WITH L' + CAST(@lvl+1 AS nvarchar) + ' AS 
        (
            SELECT
                [Level' + CAST(@lvl AS nvarchar) + '],
                [Level' + CAST(@lvl+1 AS nvarchar) + '] AS [LevelName],
                ' + CAST(@lvl+1 AS nvarchar) + ' AS [Level]
            FROM
                [dbo].[FlatHierarchy]
            WHERE
                [Level' + CAST(@lvl+1 AS nvarchar) + '] IS NOT NULL
            GROUP BY
                [Level' + CAST(@lvl AS nvarchar) + '],
                [Level' + CAST(@lvl+1 AS nvarchar) + ']
        )
        INSERT INTO #H
        SELECT
            (SELECT
                [ID]
             FROM
                #H
             WHERE
                [LevelName] = [Level' + CAST(@lvl AS nvarchar) + ']
             AND
                [Level] = ' + CAST(@lvl AS nvarchar) + ' 
             ) AS [ParentID],
            [Level],
            [LevelName]
        FROM
            L' + CAST(@lvl+1 AS nvarchar) + ';'
        PRINT @stmt;
        EXEC (@stmt);
        SET @lvl += 1;
        /*--SQL 2005*/
        --SET @lvl = @lvl + 1;
    END; 

--hierarchikus adat visszaellenőrzése
SELECT * FROM #H;


--"kilapított" hierarchia visszaellenörzése

;WITH Tree AS
(
    SELECT
        [ID],
        [ParentID],
        1 AS [Level],
        CAST([LevelName] AS nvarchar(max)) AS [Path]
    FROM
        #H
    WHERE
        [ParentID] IS NULL

    UNION ALL
     
    SELECT
        T.[ID],
        T.[ParentID],
        P.[Level] + 1,
        CAST((P.[Path] + ' | ' + T.[LevelName]) AS nvarchar(max))
    FROM
        Tree P
    JOIN
        #H T ON P.[ID] = T.[ParentID]
)

SELECT * FROM Tree ORDER BY [Level] ASC
    A.[Level] + 1,
    CAST(o.[name] + '|' + ob.name AS nvarchar(max)) ,
    fkc.[referenced_object_id]
FROM
    CTE A
JOIN
    sys.objects o on A.[object_id] = o.[object_id]
JOIN
    sys.objects ob on A.referenced_object_id = ob.[object_id]
JOIN
    sys.foreign_key_columns fkc on fkc.[parent_object_id] = o.[object_id]
)

SELECT DISTINCT
    [Level],
    [name]
FROM
    CTE
ORDER BY
    [Level] ASC