Select records in hierarchical order

Question

Select records in hierarchical order

henryvuong1000

SSC Eights!

Points: 821
More actions
April 16, 2016 at 6:13 pm

#306638

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
Sorry I have to use the underscore characters to create spaces in the table. The forum post keeps remove my spaces.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

If you click the QUOTE button on this post, you'll see how to use the "IFCode Shortcuts" that you can see to the left of your post when you're creating it. There are more codes under "IFCode" in the menu that will appear at the top of your message.

Also, help others help you. Please see the article at the first link in my signature line below for how to give your post the best chance of being solved with code rather than rhetoric. I did it for you this time in the code below, which also has a coded solution for you. As usual for me, the details are in the comments in the code.

There's also an article on the subject. I happen to know the fellow and can vouch for him. Here's the link...

http://www.sqlservercentral.com/articles/T-SQL/72503/

--=============================================================================

-- Create and populate the test table.

-- This is how you should make readily consumable data for your post.

-- Nothing in this section is a part of the solution. We're just setting

-- up test data.

--=============================================================================

--===== If the test table already exits,

-- drop it to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#Folders','U') IS NOT NULL

DROP TABLE #Folders

;

--===== Create the test table

CREATE TABLE #Folders

(

FolderID INT

,FolderName VARCHAR(20)

,ParentID INT

,OrdinalID INT

)

;

--===== Populate the test table

INSERT INTO #Folders

(FolderID,FolderName,ParentID,OrdinalID)

SELECT 1,'All Folders',0,1 UNION ALL

SELECT 2,'Folder A' ,1,1 UNION ALL

SELECT 3,'Folder A1' ,2,1 UNION ALL

SELECT 4,'Folder A2', 2,2 UNION ALL

SELECT 5,'Folder C' ,1,3 UNION ALL

SELECT 6,'Folder B2' ,7,2 UNION ALL

SELECT 7,'Folder B' ,1,2 UNION ALL

SELECT 8,'Folder B1' ,7,1 UNION ALL

SELECT 9,'Folder C1' ,5,1 UNION ALL

SELECT 10,'Folder B3' ,7,3

;

--=============================================================================

-- Solve the problem using an rCTE (Recursive CTE) along with a

-- concatenation of names to build a sort order by name.

-- Please see the following link for a detailed explanation.

-- http://www.sqlservercentral.com/articles/T-SQL/72503/

--=============================================================================

WITH cteHierarchy AS

( --=== Find the folder(s) to start with (just one in this case)

SELECT FolderID

,ParentID

,FolderName

,hLevel = 1

,SortName = CAST(FolderName AS VARCHAR(8000)) + '\'

FROM #Folders

WHERE ParentID = 0

UNION ALL

--==== Recurse through each level of the hierarchy and build the column for

-- the final sort.

SELECT tbl.FolderID

,tbl.ParentID

,tbl.FolderName

,hLevel = cte.hLevel + 1

,SortName = CAST(cte.SortName + tbl.FolderName + '\' AS VARCHAR(8000))

FROM #Folders tbl

JOIN cteHierarchy cte

ON tbl.ParentID = cte.FolderID

) --=== Display the indented folder hierarchy sorted by name

SELECT FolderName = SPACE((hLevel-1)*2) + FolderName

FROM cteHierarchy

ORDER BY SortName

;

The results look like this...

All Folders

Folder A

Folder A1

Folder A2

Folder B

Folder B1

Folder B2

Folder B3

Folder C

Folder C1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

I was going to post what Jeff posted. :Whistling:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

@henryvuong1000,

Did that do it for you or did you end up doing something else? If something else, please take a minute andpost it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

henryvuong1000 SSC Eights! Points: 821 More actions · Answer 4

Jeff, your solution works, but using the folder name as a sort does not apply for me. In the sample table, they are in alphabetical order, but in the real table they are not. Using their names to sort does not give me the intended result. This one works for me, based on your solution (changing SortName by SortKey):

WITH cteHierarchy AS

( --=== Find the folder(s) to start with (just one in this case)

SELECT FolderID

,ParentID

,FolderName

,hLevel = 1

,SortKey = CAST(Right('000000000000' + Cast(OrdinalID As varchar(11)), 11) As varchar(max))

FROM #Folders

WHERE ParentID = 0

UNION ALL

--==== Recurse through each level of the hierarchy and build the column for

-- the final sort.

SELECT tbl.FolderID

,tbl.ParentID

,tbl.FolderName

,hLevel = cte.hLevel + 1

--,SortName = CAST(cte.SortName + tbl.FolderName + '\' AS VARCHAR(8000))

,SortKey = cte.SortKey + Right('000000000000' + Cast(tbl.OrdinalID As varchar(11)), 11)

FROM #Folders tbl

JOIN cteHierarchy cte

ON tbl.ParentID = cte.FolderID

) --=== Display the indented folder hierarchy sorted by name

SELECT FolderName = SPACE((hLevel-1)*2) + FolderName

FROM cteHierarchy

ORDER BY SortKey

;

Thanks for your help

..