November 14, 2015 at 1:04 am
Hi All,
I've a table with hierarchical data relationship (parent-child) contained in one table.
How i can query the table and return the records sorted by its hierarchical level?
To those who are familiar with Oracle, i'm looking for the same thing that 'Connect by' does.
Thanks in advanced.
November 14, 2015 at 10:13 am
As you've found, SQL Server doesn't have anything quite as convenient as "CONNECT BY". It's does, have a couple of tools that you can use to do similar though. There's also the "HierarchyID" datatype, which I avoid like the plague because it's difficult for a human to fix if something goes wrong.
One of the tools is an rCTE (Recursive Common Table Expression), which is almost identical to what they call "Subquery Refactoring" in Oracle. Behind the scenes, it's not better than and is frequently worse in performance than a While Loop (especially if you know the secret behind making such While Loops really fast for a While Loop). In this case and because it processes rows in sub-sets based on level, it's not too bad. You can see how to use it in the following article.
http://www.sqlservercentral.com/articles/T-SQL/72503/
That article also contains two links to some relatively simple but seriously advanced methods of handling large hierarchies and both are worth the read if you're hierarchies aren't just simple menu selections and you need some serious processing to occur based on the hierarchy. MLM'ers and people that work with insane sized bills of materials can really benefit from those methods. Of course, so can smaller users especially if the hierarchical data isn't changing at a high rate.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply