How do I make a sql loop?
here is a simplified table
filesystem (id, name, parentId);
and some entries
(1, 'root', NULL)
(2, 'folder', 1)
(3, 'subfolder', 2)
(4, 'subsubfolder', 3)
Is there a way to use native SQL to print the absolute path of a single entry?
for example, the last entry will print "root / folder / subfolder / subsubfolder". entry 2 will print "root / folder" and so on.
+3
source to share
3 answers
You did not specify your DBMS, the following standard (ANSI):
with recursive folder_tree as (
select id, name, parentid, name as fullpath
from filesystem
where parentid is null
union all
select c.id, c.name, c.parentid, p.fullpath||'/'||c.name
from filesystem c
join folder_tree p on c.parentid = p.id
)
select *
from folder_tree
SQLFiddle: http://sqlfiddle.com/#!15/91332/7
+1
source to share
You can do something like this
with tree(id, Level, Hierarchy) as
(
select id, 0, cast(Name as varchar(max))
from filesystem
union all
select a.id, b.Level+1,
b.Hierarchy+'/'+a.Name
from filesystem a
inner join tree b on a.parentid=b.id
)
select top(1) id, Hierarchy
from tree
where id=4
order by Level desc
It will give you an ID with the full path to the file. For more details, you can check this
+1
source to share
Recursive CTE solution for SQL Server:
WITH FileSystem(id,name,parentID)
AS
(
SELECT 1,'root',NULL
UNION ALL
SELECT 2,'folder',1
UNION ALL
SELECT 3,'subFolder',2
UNION ALL
SELECT 4,'subSubFolder',3
),
CTE_Recursion
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) filePath_id,ID,CAST(name AS VARCHAR(100)) name,parentID
FROM FileSystem
WHERE parentID IS NULL
UNION ALL
SELECT A.filePath_id,B.id,CAST(A.name + '\' + B.name AS VARCHAR(100)),B.parentID
FROM CTE_Recursion A
INNER JOIN FileSystem B
ON A.ID = B.parentID
)
SELECT filePath_id,MAX(name) filePath
FROM CTE_Recursion
GROUP BY filepath_id
Results:
filePath_id filePath
-------------------- -----------------------------------
1 root\folder\subFolder\subSubFolder
-1
source to share