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