SQL Server CTE - recursion

Question

I have 2 tables, first contains data of documents and second for directories.

Table 1

DocID  DirID  Name  Order
-----  -----  ----  -----
1      4      Doc1  2
2      1      Doc2  1
3      5      Doc3  1
4      3      Doc4  1
5      4      Doc5  1

Table 2

DirID  ParentID  Name
-----  --------  ----
1      NULL      root
2      1         Dir1
3      2         Dir2
4      1         Dir3
5      3         Dir4

Structure

root
-Dir1
 -Dir2
  -Dir4
   -Doc3 
  -Doc4 
-Dir3
 -Doc5 
 -Doc1 
-Doc2

I'm trying to create CTE in T-SQL, which will generate this result, but I can't figure out how to do it. Can somebody suggest a solution?

Doc2
Dir3/Doc5
Dir3/Doc1
Dir1/Dir2/Doc4
Dir1/Dir2/Dir4/Doc3

Root is not shown, documents are sorted by Order within its directory and the result is sorted from the lowest depth ordered by name of full path.

Note order column name is a reserved word careful with syntax — xQbert
– xQbert, Commented Jul 20, 2015 at 18:33

Matt · Accepted Answer · 2018-02-28 12:09:08Z

Use CTE for generating folder hierarchy
Then join documents to the folders
And order result using created Path for that purpose

DECLARE @Separator AS VARCHAR(1) = '\\'
--Generating folder hierarchy
;WITH Info AS
(
    SELECT f.DirID
    , f.ParentID
    , f.Name
    , CAST(f.Name AS VARCHAR(255)) AS PathValue
    FROM Folders f
    WHERE f.ParentID IS NULL

    UNION ALL

    SELECT f.DirID
    , f.ParentID
    , f.Name
    , CAST(i.PathValue + @Separator + f.Name AS VARCHAR(255))
    FROM Folders f
    INNER JOIN Info i ON f.ParentID = i.DirID
)

-- Join documents to the folder hirarchy
SELECT i.ParentID
, i.DirID
, i.Name
, d.DocID
, d.Name
, i.PathValue + @Separator + CAST(d.OrderNum AS VARCHAR(255)) + '.' + d.Name AS OrderPath
, i.PathValue + @Separator + d.Name AS DocumentPath
FROM Info i
INNER JOIN Documents d ON d.DirID = i.DirID

UNION ALL

-- Adding NULL row for folder, will show folder in the result even no documents
-- This can be removed if you want show only folders which containing documents
SELECT i.ParentID
, i.DirID
, i.Name
, NULL
, NULL
, i.PathValue AS OrderPath
, i.PathValue AS DocumentPath
FROM Info i
ORDER BY OrderPath

SQL Fiddle

Matt · Accepted Answer · 2018-02-28 12:07:30Z

2

Using Recursive CTE, you could easily create a path as shown above:

NOTE: I believe your output is wrong for Doc4: Dir1/Dir2/Doc4. It should be Dir1/Doc4 per logic.

;WITH q1
AS (
    SELECT a.DocID
        ,b.*
        ,a.NAME AS rootname
    FROM tableb b
    LEFT JOIN tablea a ON b.DirID = a.DirID
    )
,q2
AS (
    -- anchor 
    SELECT DocID
        ,DirID
        ,q1.rootname
        ,ParentID
        ,CAST((q1.NAME) AS VARCHAR(1000)) [Path]
    FROM q1
    WHERE ParentId IS NULL

    UNION ALL

    --recursive member 
    SELECT t.DocID
        ,t.DirID
        ,t.rootname
        ,t.ParentID
        ,CAST((a.path + '/' + t.NAME) AS VARCHAR(1000)) [Path]
    FROM q1 AS t
    INNER JOIN q2 AS a ON t.ParentId = a.DirID
    )
SELECT replace([Path] + '/' + q2.rootname, 'root/', '') AS FinalPath
FROM q2
WHERE q2.rootname IS NOT NULL
ORDER BY FinalPath DESC

SQL Fiddle Demo

edited Feb 28, 2018 at 12:07

Matt

27.4k19 gold badges131 silver badges202 bronze badges

answered Jul 20, 2015 at 20:16

FutbolFan

13.9k3 gold badges27 silver badges35 bronze badges

1 Comment

Triber Over a year ago

Sorry for late response, I just went through some older questions and I missed this one. If I remember well, the second answer is longer, but it seemed clearer to me, but both work.

Collectives™ on Stack Overflow

SQL Server CTE - recursion

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related