Flat Hierarchiából "hagyományos" hierachikus adathalmaz
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):
1/***********************************************
2This code is provided AS IS, without warranty!!!
3***********************************************/
4
5USE tempdb;
6GO
7
8IF EXISTS (SELECT 1 FROM sys.objects WHERE [OBJECT_ID] = OBJECT_ID('[dbo].[FlatHierarchy]'))
9 DROP TABLE [dbo].[FlatHierarchy];
10GO
11
12--"kilapított" hierarchia adatok tárolására szolgáló tábla
13CREATE TABLE [dbo].[FlatHierarchy]
14(
15 [Level1] varchar(100) NOT NULL,
16 [Level2] varchar(100) NULL,
17 [Level3] varchar(100) NULL,
18 [Level4] varchar(100) NULL,
19 [Level5] varchar(100) NULL,
20 [LevelData] varchar(100) NULL
21);
22GO
23
24--minta adatok (SQL 2008 alatt csak, korábbi verzióknál a kommentelt részt kell használni)
25INSERT INTO [dbo].[FlatHierarchy] ([Level1], [Level2], [Level3], [Level4], [Level5], [LevelData])
26VALUES
27('Level1', NULL, NULL, NULL, NULL, 1),
28('Level1', 'Level2', NULL, NULL, NULL, 2),
29('LevelA', 'LevelTwo', NULL, NULL, NULL, 2),
30('Level1', 'Level2', NULL, NULL, NULL, 3),
31('Level1', 'Level2', 'Level3', 'Level4', NULL, 4),
32('Level1', 'Level2', 'Level3', 'Level4', 'Level5', 4);
33/*--SQL 2005 vagy korábbi*/
34--INSERT INTO [dbo].[FlatHierarchy] ([Level0], [Level1], [Level2], [Level3], [Level4], [Level5], [LevelData])
35--SELECT 'Level1', NULL, NULL, NULL, NULL, 1
36--UNION
37--SELECT 'Level1', 'Level2', NULL, NULL, NULL, 2
38--UNION
39--SELECT 'LevelA', 'LevelTwo', NULL, NULL, NULL, 2
40--UNION
41--SELECT 'Level1', 'Level2', NULL, NULL, NULL, 3
42--UNION
43--SELECT 'Level1', 'Level2', 'Level3', 'Level4', NULL, 4
44--UNION
45--SELECT 'Level1', 'Level2', 'Level3', 'Level4', 'Level5', 4
46GO
47
48--adatok visszaellenőrzése
49SELECT * FROM [dbo].[FlatHierarchy];
50GO
51
52
53--hierarchia felépítése
54
55IF EXISTS (SELECT 1 FROM sys.objects WHERE [OBJECT_ID] = OBJECT_ID('#H'))
56 DROP TABLE #H;
57GO
58
59CREATE TABLE #H
60
61(
62 [ID] int identity NOT NULL,
63 [ParentID] int NULL,
64 [Level] int NOT NULL,
65 [LevelName] varchar(100) NOT NULL
66);
67GO
68
69DECLARE @lvl int;
70DECLARE @stmt nvarchar(max);
71SET @lvl = 1;
72
73--Level 1 felépítése
74INSERT INTO #H
75SELECT
76 NULL,
77 1,
78 [Level1] AS [LevelName]
79FROM
80 [dbo].[FlatHierarchy]
81GROUP BY
82 [Level1];
83
84--Level2 - Level5 felépítése
85WHILE @lvl < 5
86 BEGIN
87 SET @stmt = N'
88 ;WITH L' + CAST(@lvl+1 AS nvarchar) + ' AS
89 (
90 SELECT
91 [Level' + CAST(@lvl AS nvarchar) + '],
92 [Level' + CAST(@lvl+1 AS nvarchar) + '] AS [LevelName],
93 ' + CAST(@lvl+1 AS nvarchar) + ' AS [Level]
94 FROM
95 [dbo].[FlatHierarchy]
96 WHERE
97 [Level' + CAST(@lvl+1 AS nvarchar) + '] IS NOT NULL
98 GROUP BY
99 [Level' + CAST(@lvl AS nvarchar) + '],
100 [Level' + CAST(@lvl+1 AS nvarchar) + ']
101 )
102 INSERT INTO #H
103 SELECT
104 (SELECT
105 [ID]
106 FROM
107 #H
108 WHERE
109 [LevelName] = [Level' + CAST(@lvl AS nvarchar) + ']
110 AND
111 [Level] = ' + CAST(@lvl AS nvarchar) + '
112 ) AS [ParentID],
113 [Level],
114 [LevelName]
115 FROM
116 L' + CAST(@lvl+1 AS nvarchar) + ';'
117 PRINT @stmt;
118 EXEC (@stmt);
119 SET @lvl += 1;
120 /*--SQL 2005*/
121 --SET @lvl = @lvl + 1;
122 END;
123
124--hierarchikus adat visszaellenőrzése
125SELECT * FROM #H;
126
127
128--"kilapított" hierarchia visszaellenörzése
129
130;WITH Tree AS
131(
132 SELECT
133 [ID],
134 [ParentID],
135 1 AS [Level],
136 CAST([LevelName] AS nvarchar(max)) AS [Path]
137 FROM
138 #H
139 WHERE
140 [ParentID] IS NULL
141
142 UNION ALL
143
144 SELECT
145 T.[ID],
146 T.[ParentID],
147 P.[Level] + 1,
148 CAST((P.[Path] + ' | ' + T.[LevelName]) AS nvarchar(max))
149 FROM
150 Tree P
151 JOIN
152 #H T ON P.[ID] = T.[ParentID]
153)
154
155SELECT * FROM Tree ORDER BY [Level] ASC
156 A.[Level] + 1,
157 CAST(o.[name] + '|' + ob.name AS nvarchar(max)) ,
158 fkc.[referenced_object_id]
159FROM
160 CTE A
161JOIN
162 sys.objects o on A.[object_id] = o.[object_id]
163JOIN
164 sys.objects ob on A.referenced_object_id = ob.[object_id]
165JOIN
166 sys.foreign_key_columns fkc on fkc.[parent_object_id] = o.[object_id]
167)
168
169SELECT DISTINCT
170 [Level],
171 [name]
172FROM
173 CTE
174ORDER BY
175 [Level] ASC