Getting a hierarchical structure using SQL Server
I have a self-regulation table with a primary key id
and a foreign key parent_id
.
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PK | NULL | IDENTITY |
| parent_id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
I have a table like the following (minify data for clearer)
Table MySiteMap
Id Name parent_id
1 A NULL
2 B 1
3 C 1
4 D 1
20 B1 2
21 B2 2
30 C1 3
31 C2 3
40 D1 4
41 D2 4
I would like to get a hierarchical structure using a SQL Server query:
A
|
B
|
| B1
| B2
C
|
| C1
| C2
D
|
| D1
| D2
Any suggestions?
You can use Common table expressions .
WITH LeveledSiteMap(Id, Name, Level)
AS
(
SELECT Id, Name, 1 AS Level
FROM MySiteMap
WHERE Parent_Id IS NULL
UNION ALL
SELECT m.Id, m.Name, l.Level + 1
FROM MySiteMap AS m
INNER JOIN LeveledSiteMap AS l
ON m.Parent_Id = l.Id
)
SELECT *
FROM LeveledSiteMap
Use this:
;WITH CTE(Id, Name, parent_id, [Level], ord) AS (
SELECT
MySiteMap.Id,
CONVERT(nvarchar(255), MySiteMap.Name) AS Name,
MySiteMap.parent_id,
1,
CONVERT(nvarchar(255), MySiteMap.Id) AS ord
FROM MySiteMap
WHERE MySiteMap.parent_id IS NULL
UNION ALL
SELECT
MySiteMap.Id,
CONVERT(nvarchar(255), REPLICATE(' ', [Level]) + '|' + REPLICATE(' ', [Level]) + MySiteMap.Name) AS Name,
MySiteMap.parent_id,
CTE.[Level] + 1,
CONVERT(nvarchar(255),CTE.ord + CONVERT(nvarchar(255), MySiteMap.Id)) AS ord
FROM MySiteMap
JOIN CTE ON MySiteMap.parent_id =CTE.Id
WHERE MySiteMap.parent_id IS NOT NULL
)
SELECT Name
FROM CTE
ORDER BY ord
For this:
A
| B
| B1
| B2
| C
| C1
| C2
| D
| D1
| D2
I know that changing the structure of a table is always a critical operation, but since SQL Server 2008 introduced the HierarchyId data type, I really enjoy working with it. Maybe take a look:
http://www.codeproject.com/Articles/37171/HierarchyID-Data-Type-in-SQL-Server http://www.codeproject.com/Tips/740553/Hierarchy-ID-in-SQL-Server
I am sure you will quickly understand how to use this data type and its functions. SQL code using this data type is more structured and performs better than CTEs.
I started with a request (but when I check it now, it looks like Mark.)
I'll add anyway, while I created also a sqlfiddle with my and Mark query.
WITH tList (id,name,parent_id,nameLevel)
AS
(
SELECT t.id, t.name, t.parent_id, 1 AS nameLevel
FROM t as t
WHERE t.parent_id IS NULL
UNION ALL
SELECT tnext.id, tnext.name, tnext.parent_id, tList.nameLevel + 1
FROM t AS tnext
INNER JOIN tList AS tlist
ON tnext.parent_id = tlist.id
)
SELECT id,name,isnull(parent_id,0) 'parent_id',nameLevel FROM tList order by nameLevel;
Good blog: SQL Query - how to get data in a hierarchical structure?