Select records in hierarchical order

1

I have an SQL Server table like this:

`Table: Folders
FolderID (int) 	Folder Name (varchar) 	ParentID (int)	OrdinalID (int)
1		All Folders			0		1
2		Folder A			1		1
3 		Folder A1			2		1
4		Folder A2			2		2
5 		Folder C			1		3
6		Folder B2			7		2
7		Folder B			1		2
8		Folder B1			7		1
9 		Folder C1			5		1
10		Folder B3			7		3

`

The ParentID of a folder is the ID of its parent folder, the ordinalID is the order of folders in the same level. I need a script to select the folders in this order (the indentation is optional):

All Folders
   Folder A
      Folder A1
      Folder A2
   Folder B
      Folder B1
      Folder B2
      Folder B3
   Folder C
      Folder C1

Please advise. Thanks

2

One solution could be:

with cte
  as (select a.folderid
            ,a.[folder name]
            ,a.parentid
            ,a.ordinalid
            ,cast(a.ordinalid as varchar(max)) as levelno
        from #folders as a
             left outer join #folders as b
                          on b.folderid=a.parentid
       where b.folderid is null
      union all
      select a.folderid
            ,a.[folder name]
            ,a.parentid
            ,a.ordinalid
            ,b.levelno+'.'+cast(a.ordinalid as varchar(max)) as levelno
        from #folders as a
             inner join cte as b
                     on b.folderid=a.parentid
     )
select replicate(' ',len(levelno)-1)
      +[folder name]
       as [folder name]
  from cte
 order by levelno
;
1 Like
3

Your solutions works like a charm. Thanks