September 7, 2013 at 9:09 pm
I want to write a query to flatten the hierarchy of a table and I don't know what's best way to do it.
Here' s something about the data I am dealing with:
We know Manager-Employee table is a typical example of table with parent_child relationship, we can easily
use a recursive CTE to find the hierarchy relationship with different hierarchy level. If we treat Manager-employee as one company that has parent-child relationship then the table I am currently dealing with will be a table contains many companies that I need to find the hirarchy relationship for each one of the company in the table and companies in the table has no relationship with each other.
The final output will be the union of recursive CTE for each company.
If the table contains A, B, C, D and E five companies
the output will be
Recursive CTE_A union Recursive CTE_B unoin Recursive CTE_C ...union Recursive CTE_E
apparently in the real table it contains millions of individual records or companies.
What's the best way to achieve it?
Thank you!
September 8, 2013 at 4:12 pm
A lot depends on what format the original hierarchy is stored in. You may find that Jeff Moden's articles on converting hierarchies represented by loop-free adjacency lists to a much more useful table representation based on nested sets, with the usual flattened hierarchy order of the rows specified by a sort path column, gives you a good starting point. The first article is at http://www.sqlservercentral.com/articles/Hierarchy/94040/ and the second at http://www.sqlservercentral.com/articles/T-SQL/94570/, although the second may or may not be relevant depending on what you want to do with the flattened hierarchy once you've got it.
If you use Jeff's technique, I think you just need to (a) ensure that every node is tagged with the appropriate company ID before you start flattening, and (b) execute the technique once for each record with a null uplink (since that will be the top node for a company), passing in the uplink-free node as a starting point. Then you can cluster the union of the results on a sort key consisting of two columns: company ID and sort string. That would probably require some small modifications to the code Jeff has supplied.
Tom
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply