CODEWITHSUNDEEP
sqlsql-serverrecursive-query
josmond
josmond

Reputation: 7

SQL CTE Recursive Structure

I am trying to read from Sql a virtual file system structure. I would like to be able to use the child/parent recurse, but then be able to change a folder name and then all children from the old folder name show the new folder name.

--Create temporary table

CREATE TABLE #Objects (
    child VARCHAR(20),
    parent VARCHAR(20),
    ObjectType int
)

--Insert some rows

INSERT INTO #Objects (child,parent,ObjectType) VALUES ('Folder1',NULL,1)
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('File1','Folder1',0)
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('File2','Folder1',0)
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('Folder2','Folder1',1)  -- Folder1 gets renamed to Folder2
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('File3','Folder2',0)

--Try to recurse

;WITH cteLevelOne (parent, child, Depth) AS
(
        SELECT a.parent, a.child, 1 as Depth
        FROM #Objects AS a
        WHERE ObjectType = 1  -- Folder
            AND Parent IS NULL
    UNION ALL

        SELECT a.parent, a.child, c.Depth + 1 as Depth
        FROM #Objects AS a
        INNER JOIN cteLevelOne AS c ON a.child = c.parent 
        WHERE ObjectType = 0 -- File
)

select * from CTELevelone

Output received

parent  child   Depth
NULL    Folder1 1

required output (files and their current folder)

--Folder2   File1
--Folder2   File2
--Folder2   File3

Can anyone help?

Upvotes: 0

Views: 96

Answers (2)

siggemannen
siggemannen

Reputation: 9274

If i understood you correctly, you want something like this:

CREATE TABLE #Objects (
    child VARCHAR(20),
    parent VARCHAR(20),
    ObjectType int
)

INSERT INTO #Objects (child,parent,ObjectType) 
VALUES ('Folder1',NULL,1)
,('File1','Folder1',0)
,('File2','Folder1',0)
,('Folder2','Folder1',1)  -- Folder1 gets renamed to Folder2
,('File3','Folder2',0)
,('Folder3','Folder2',1)
,('File4','Folder3',0) 
,('Folder4',NULL,1)
,('Folder5',NULL,1)
,('Folder6','Folder5',1)
,('File5','Folder5',0) 
,('File6','Folder6',0) 

;WITH cte AS (
    SELECT  child AS path, child AS parent
    ,   1 AS level
    FROM    #objects so
    WHERE   parent IS NULL
    AND ObjectType = 1
    UNION ALL
    SELECT  c.path, o.child
    ,   level + 1
    FROM    cte c
    INNER JOIN #Objects o
        ON  o.parent = c.parent
        AND ObjectType = 1
    )
SELECT  o.child, latest.parent as filePath
FROM    #Objects o
INNER JOIN cte c -- Get the real parent
    ON  c.parent = o.parent
CROSS apply (   -- Get the most recent level for the path
        SELECT  TOP 1 parent
        FROM    cte c2
        WHERE   c2.path = c.path
        ORDER BY level DESC
    ) latest
WHERE   o.ObjectType = 0

I create a cte which builds a original root folder for each path. Then for each file, one can go through the parent folder back to original root folder, and finally get the most recent folder name for the original root.

Upvotes: 0

nbk
nbk

Reputation: 49395

I don't have a 2008 r2 any more for some years, but i would repace first the names and then make the recursive CTE

WITH First_rename as (
  SELECT child, Parent FROM #Objects          
  WHERE ObjectType = 1  -- Folder
            AND Parent IS NOT NULL),
  replace_folder as (
  SELECT COALESCE(f1.child,o.child) [child],COALESCE(f.child,o.parent) [parent],ObjectType
   fROM #Objects o LEFT JOIN First_rename f ON o.parent = f.parent
  LEFT JOIN First_rename f1 ON o.child = f1.parent
  )
,
cteLevelOne (parent, child, Depth) as 
(
        SELECT a.parent, a.child, 1 as Depth
        FROM replace_folder AS a
        WHERE ObjectType = 1  -- Folder
            AND Parent IS NULL
    UNION ALL

        SELECT a.parent, a.child, c.Depth + 1 as Depth
        FROM replace_folder AS a
        INNER JOIN cteLevelOne AS c ON c.child = a.parent 
        WHERE ObjectType = 0 -- File
)

select * from CTELevelone WHERE Parent is  NOT NULL
parent child Depth
Folder2 File1 2
Folder2 File2 2
Folder2 File3 2

fiddle

Upvotes: 0

Related Questions