how to get full 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)

how to get full path

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-03-09 : 16:18:36

Hello,

I have a table like below structure, how do i list ID and FullPath from below data, please see output list.
In this table, id is PK, ParentID is related with ID, fType 0 mean root folder, 1 is folder or sub-folder and 2 is File.

declare @tbl1 table
(ID varchar(20),
ParentID varchar(20),
fType int,
fName varchar(20)
)

INSERT INTO @tbl1
SELECT 'A101',NULL ,0,'RootFolder'

INSERT INTO @tbl1
SELECT 'A102','A101' ,1,'1Folder'

INSERT INTO @tbl1
SELECT 'A103','A101' ,1,'2Folder'

INSERT INTO @tbl1
SELECT 'A104','A103' ,1,'3Folder'

INSERT INTO @tbl1
SELECT 'A105','A104' ,1,'4Folder'

INSERT INTO @tbl1
SELECT 'A106','A102' ,2,'File1'

INSERT INTO @tbl1
SELECT 'A107','A102' ,2,'File2'

INSERT INTO @tbl1
SELECT 'A108','A103' ,2,'File1'

INSERT INTO @tbl1
SELECT 'A109','A103' ,2,'File2'

INSERT INTO @tbl1
SELECT 'A110','A104' ,2,'File1'

INSERT INTO @tbl1
SELECT 'A111','A104' ,2,'File2'

INSERT INTO @tbl1
SELECT 'A112','A105' ,2,'File1'

select * from @tbl1

--Output looks like this
A106 RootFolder\1Folder\File1
A107 RootFolder\1Folder\File2
A108 RootFolder\2Folder\File1
A109 RootFolder\2Folder\File2
A110 RootFolder\2Folder\3Folder\File1
A111 RootFolder\2Folder\3Folder\File2
A112 RootFolder\2Folder\3Folder\4Folder\File1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-09 : 18:37:27

You can do this using a recursive CTE. It is similar to the example that they have on MSDN http://msdn.microsoft.com/en-us/library/ms186243.aspx

with cte as
(
   select
      ID,
      ParentId,
      ftype,
      cast(fName as varchar(max)) as FullPathName
   from
      @tbl1
   where
      ParentId is null
   union all
   select
      ta.ID,
      ta.ParentId,
      ta.ftype,
      tb.FullPathName+'\'+ta.fname
   from
      @tbl1 ta
      inner join cte tb on ta.ParentId = tb.id
) select * from cte where ftype = 2 order by Id

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-03-09 : 22:00:01

perfect thanks Sunitabeck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources