Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Displaying Sorted Hierarchies (SQL Spackle)


Displaying Sorted Hierarchies (SQL Spackle)

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45456 Visits: 39946
Wesley Brown (3/10/2011)
I prefer to use a two column approach to build a tree instead of the single column style that you have to split, it doesn't scale well. For smaller stuff like this HR representation it should be OK. Trees and Hierarchies in SQL from Joe Celko is one of my all time favorite books! Good article!
your brother,
Phil McCrevice


If, by "two column approach", you mean "Nested Sets", I absolutely agree. This article was meant just to get folks that don't want to or can't convert to "Nested Sets" out of the woods for hierarchy structural sorting as has been asked in a very large number of posts here on SSC.

In the larger article I'm writing on hierarchies, I'll actually demonstrate a new method for converting an "Adjaceny List" to a "Nested Set" that I think you'll like especially since it gets away from the RBAR of a "push stack" to build the "Nested Set". Ben-Gan also has an alternate method for doing the same thing.

You'll also like the "warehouse" table that I'll build in the coming article which you might prefer to a "Nested Set" because the "warehouse" table contains preaggregated answers for the 4 of the more common hierarchical lookups that people seem to do on a regular basis.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
cedarhillr
cedarhillr
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 3
If one used the same method but inverted the list you have a roll up you can use for various accounting functions. Inverted list processing was one of the first methods the early dbs' used for performance. It's a nifty feature to learn.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45456 Visits: 39946
james-1071007 (3/10/2011)

That doesn't order correctly. You are relaying on the natural data order to provide the correct results.

Try inserting some extra records with lower manager id's and names beginning with z Wink



If you mean the natural order of the data in the hierarchical path, then, yes, I agree. The purpose of the article wasn't to necessarily sort by ID... it was meant to produce an output where the nodes where in order by their superiors instead of just by ID or just by name. To put them in actual numeric order, you'd have to convert the ID's to Binary(4) and concatenate into VARBINARY() sans slashes and that was beyond the simple scope of this article.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45456 Visits: 39946
GSquared (3/10/2011)
I've used a variation on this solution for sorting before, and found that it works best if you pad numerical values with leading zeroes to a fixed length, before sorting them as character data.


Hi Gus,

Thanks for stopping by. I agree... even better is the method of converting numeric ID data to BINARY(4) data and concatenating that information into a VARBINARY() column... sans slashes, of course.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45456 Visits: 39946
Sorry... I double posted by mistake and removed the post from this frame.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45456 Visits: 39946
cedarhillr (3/10/2011)
If one used the same method but inverted the list you have a roll up you can use for various accounting functions. Inverted list processing was one of the first methods the early dbs' used for performance. It's a nifty feature to learn.


Do you, by any chance, have a link that you could share for the "Inverted list processing" methods you speak of? I've not personnaly used the method and wouldn't mind reading up on it. Thanks.

I've developed another method which preaggregates much of the information that most folks would use a "Nested Set" for and it's directed at some of those accounting functions you speak of. I'm covering that new method in the larger article I'm writing.


Thanks for stopping by and leaving a note. I appreciate 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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14387 Visits: 9729
Jeff Moden (3/10/2011)
Wesley Brown (3/10/2011)
I prefer to use a two column approach to build a tree instead of the single column style that you have to split, it doesn't scale well. For smaller stuff like this HR representation it should be OK. Trees and Hierarchies in SQL from Joe Celko is one of my all time favorite books! Good article!
your brother,
Phil McCrevice


If, by "two column approach", you mean "Nested Sets", I absolutely agree. In the larger article I'm writing on hierarchies, I'll actually demonstrate a new method for converting an "Adjaceny List" to a "Nested Set" that I think you'll like especially since it gets away from the RBAR of a "push stack" to build the "Nested Set". Ben-Gan also has an alternate method for doing the same thing.

You'll also like the "warehouse" table that I'll build in the coming article which you might prefer to a "Nested Set" because the "warehouse" table contains preaggregated answers for the 4 of the more common hierarchical lookups that people seem to do on a regular basis.


I've got a two-step script that can generate a nested sets "path" from an adjacency hierarchy, turning it into a nested sets hierarchy, using Cross Apply and a ranking function. Single query, and much more efficient than the method Joe originally proposed (but also uses features he didn't have when he originally wrote the article).

I've found you can also short-circuit the conversion and get really good performance using the ASCII table and a binary colation for your set headers and ranges instead of numeric values. You don't need the binary colation for smaller hierarchies, but it comes in handy for bigger/complex ones with lots of top levels. (This method takes advantage of the fact that you can, in the recursive part of the CTE, concat a string together based on level and row_number within the level, and that "AAAB" is between "AAA" and "AAB" in a string sort. Much simpler than using integers for the ranges.)

The string-range version is a bit of a pain to set up in the first place (making sure all top level and sub-level strings are guaranteed unique out of a limited dataset of ASCII characters that can be sorted on), but works nicely once it's in place.

I'm interested in seeing how your article solves it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45456 Visits: 39946
GSquared (3/10/2011)
I've got a two-step script that can generate a nested sets "path" from an adjacency hierarchy, turning it into a nested sets hierarchy, using Cross Apply and a ranking function.


Is that method in the good article you wrote on hierarchies, Gus?

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6016 Visits: 8314
I use 'generated' TSQL like that all the time but never thought to make the leap and intermix straight/generated TSQL in a CTE like that. Oh the possibilities!! Nicely done Jeff, as usual!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14387 Visits: 9729
Jeff Moden (3/10/2011)
GSquared (3/10/2011)
I've got a two-step script that can generate a nested sets "path" from an adjacency hierarchy, turning it into a nested sets hierarchy, using Cross Apply and a ranking function.


Is that method in the good article you wrote on hierarchies, Gus?


Nope. Developed after that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search