I have a self-referencing 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 got a table as following (reduce data for more clear)
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 get the hierarchical structure using 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With