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?
source to share
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
source to share
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
source to share
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.
source to share
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?
source to share