Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Flatten a hierarchy table Expand / Collapse
Posted Saturday, September 7, 2013 9:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 9, 2016 2:38 PM
Points: 41, Visits: 124
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!
Post #1492573
Posted Sunday, September 8, 2013 4:12 PM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 9,822, Visits: 11,891
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 and the second at, 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.

Post #1492626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse