CODEWITHSUNDEEP
sqlsql-servert-sql
Saturn K
Saturn K

Reputation: 2785

How do I grab nested data 3 levels deep in SQL Server self-referencing table

ID  ParentID    Name
1   NULL        Adobe     
2   1           Adobe Flash
3   2           Adobe Flash 1
4   3           Adobe Flash 2
5   4           Adobe Flash 3
6   NULL        Visual Studio
7   6           Visual Studio Beginners
8   NULL        SQL Server
9   6           Visual Studio Intermediate
10  6           Visual Studio Advanced

I want to write a query that nests 4 levels deep to display the result-set: 

1.      Adobe
1.1     Adobe Flash
1.1.1   Adobe Flash 2
1.1.1.1 Adobe FLash 3
2.      Visual Studio
2.1     Visual Studio Beginners
3.      SQL Server
2.2     Visual Studio Intermediate
2.3     Visual Studio Advanced

Any help is greatly appreciated

Upvotes: 0

Views: 397

Answers (2)

Rubens Farias
Rubens Farias

Reputation: 57976

WITH SelfReferencingTable AS
(   SELECT  *
    FROM    (   VALUES
                (01, NULL   , 'Adobe'                       ),
                (02, 01     , 'Adobe Flash'                 ),
                (03, 02     , 'Adobe Flash 1'               ),
                (04, 03     , 'Adobe Flash 2'               ),
                (05, 04     , 'Adobe Flash 3'               ),
                (06, NULL   , 'Visual Studio'               ),
                (07, 06     , 'Visual Studio Beginners'     ),
                (08, NULL   , 'SQL Server'                  ),
                (09, 06     , 'Visual Studio Intermediate'  ),
                (10, 06     , 'Visual Studio Advanced'      )
            )   SRT(ID, ParentID, Name)
),
Levels AS
(   SELECT  SRT.Id, SRT.ParentId, SRT.Name, 
            0 as Level, -- To control maximum recurrence
            -- Concatenate row number order by Id (try with name)
            CAST(ROW_NUMBER() 
                OVER (ORDER BY SRT.ID) AS VARCHAR(MAX)) + '.' AS Path
    FROM    SelfReferencingTable SRT
    WHERE   ParentId IS NULL
    UNION ALL   -- Do the recursion, baby
    SELECT  SRT.Id, SRT.ParentId, SRT.Name, Level + 1, Levels.Path + 
            CAST(ROW_NUMBER() OVER (ORDER BY SRT.ID) AS VARCHAR) + '.'
    FROM    Levels
    JOIN    SelfReferencingTable SRT
        ON  SRT.ParentID = Levels.ID
)
SELECT      Path, Name
FROM        Levels
--WHERE     Level < 3   -- If you really need to limit maximum recursion
ORDER BY    Path

Results

Path        Name
----------- ---------------------
1.          Adobe
1.1.        Adobe Flash
1.1.1.      Adobe Flash 1
1.1.1.1.    Adobe Flash 2
1.1.1.1.1.  Adobe Flash 3
2.          Visual Studio
2.1.        Visual Studio Beginners
2.2.        Visual Studio Intermediate
2.3.        Visual Studio Advanced
3.          SQL Server

Upvotes: 2

Karl Kieninger
Karl Kieninger

Reputation: 9149

Some notes before a solution:

  1. Assumes a recent version of SQL Server.
  2. This doesn't stop at 3 levels deep, but you could easily restrict if that is necessary.
  3. This is probably an egregious use of the database--the putting of the hierarchy in a outline style number list is almost certainly better done in you presentation layer. (The first bit which does the initial hierarchy is pretty standard).
  4. I've no idea how this will scale if your list is very large.
  5. I'm using a table valued constructor to populate the initial dataset into a CTE for no particualr reason. I could have built a table, temp temple, table variable just as easily.
  6. One might be able to do this in few steps, but I'd have to think on it more.

This is what I come up with:

WITH tbl AS (
 SELECT * 
   FROM (VALUES (1,NULL,'Adobe')     
               ,(2,1,'Adobe Flash')
               ,(3,2,'Adobe Flash 1')
               ,(4,3,'Adobe Flash 2')
               ,(5,4,'Adobe Flash 3')
               ,(6,NULL,'Visual Studio')
               ,(7,6,'Visual Studio Beginners')
               ,(8,NULL,'SQL Server')
               ,(9,6,'Visual Studio Intermediate')
               ,(10,6,'Visual Studio Advanced')) a(ID,ParentID,Name)
)
, initial AS (
  SELECT ID
        ,ParentID
        ,Name
            ,CONVERT(VARCHAR(MAX),ISNULL(ID,0)) as Path
        ,CAST('' AS VARCHAR(MAX)) AS PathGroup
    FROM tbl
   WHERE ParentID IS NULL
   UNION ALL
  SELECT tbl.ID
        ,tbl.ParentID
        ,tbl.Name
          ,initial.Path + '.' + CONVERT(VARCHAR(MAX),tbl.ID) 
        ,initial.Path 
    FROM tbl
         INNER JOIN 
         initial ON tbl.ParentId = initial.ID
)
, middle AS (
  SELECT *
        ,ROW_NUMBER() OVER(PARTITION BY PathGroup ORDER BY Path) rn
    FROM initial
)
, final AS (
  SELECT ID
        ,ParentID
        ,Name
            ,CONVERT(VARCHAR(MAX),rn) Hierarchy
    FROM middle
   WHERE ParentID IS NULL
   UNION ALL
  SELECT middle.ID
        ,middle.ParentID
        ,middle.Name
          ,final.Hierarchy + '.' + CONVERT(VARCHAR(MAX),middle.rn) 
    FROM middle
         INNER JOIN 
         final ON middle.ParentId = final.ID
)
SELECT Hierarchy
      ,Name 
  FROM final  
 ORDER BY Hierarchy

Upvotes: 2

Related Questions