Get hierarchical structure using SQL Server

Questions

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?

Answers

https://msdn.microsoft.com/en-us/library/ms175972.aspx https://msdn.microsoft.com/en-us/library/ms175972.aspx

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

License : cc by-sa 3.0

http://stackoverflow.com/questions/30344131/get-hierarchical-structure-using-sql-server

Related