Determine hierarchical path - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Determine hierarchical path

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2010-02-20 : 12:49:30

I have a Folders table which stores a hierarchical set of records as well as a Files table. These two combine to form the basis of a document management system. When retrieveing File records, I would like to also be able to retrieve the folder path for the file. The structures are as follows:

Folders (Folder_Id, FolderName, Parent_Id)
Files (File_Id, FileName, Folder_Id)

Sample Data:

Folders:

"F1", "Folder 1", NULL
"F2", "Folder 2", NULL
"F1A", "Folder 1A", "F1"
"F1A1", "Folder 1A1", "F1A"
"F1B", "Folder 1B", "F1"
"F2A", "Folder 2A", "F2"

Files:

"File1", "Some File", "F1"
"File2", "Another File", "F1A1"
"File3", "Yet Another File", "F1B"

So, I would like to return Files Records like so...

File_Id, FileName, FolderName, FolderPath

"File1", "Some File", "Folder 1", "Folder 1"

"File2", "Another File", "Folder 1A1", "Folder 1 > Folder 1 A > Folder 1A1"

"File3", "Yet Another File", "Folder 1B", "Folder 1 > Folder 1B"

Ideally, I would like to pass the delimeter used in the FolderPath field as a parameter.

Since I am returning the foldername of the folder the file is in, I don't necessarily have to include that in the folderpath, as I have done in this example.

Sachin.Nand

2937 Posts

Posted - 2010-02-20 : 14:05:35

[code]
declare @Folders as table(Folder_Id varchar(30), FolderName varchar(60), Parent_Id varchar(30))
insert into @Folders
select 'F1', 'Folder 1', NULL union all
select 'F2', 'Folder 2', NULL union all
select 'F1A', 'Folder 1A', 'F1' union all
select 'F1A1', 'Folder 1A1', 'F1A' union all
select 'F1B', 'Folder 1B', 'F1' union all
select 'F2A', 'Folder 2A', 'F2'
declare @Files as table(File_Id varchar(30), FileName varchar(30), Folder_Id varchar(30))
insert into @Files
select 'File1', 'Some File', 'F1' union all
select 'File2', 'Another File', 'F1A1' union all
select 'File3', 'Yet Another File', 'F1B'

select * from @Folders
select * from @Files
;with cte
as
(
select FolderName,Folder_Id,Parent_Id,convert(varchar(100),FolderName)as Folderpath from @Folders F
union all
select Fi.FolderName,Fi.Folder_Id,Fo.Parent_Id,convert(varchar(100),Fo.Folderpath + '>' + Fi.FolderName ) from @Folders Fi inner join cte Fo
on Fo.Folder_Id=Fi.Parent_Id

)

select File_Id,FileName,FolderName,Folderpath from cte C
inner join @Files F on C.Folder_Id=F.Folder_Id
where C.Parent_Id IS NULL
order by F.Folder_Id
option (maxrecursion 0)
[/code]

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources