SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Flatten a hierarchy table


Flatten a hierarchy table

Author
Message
MaggieW
MaggieW
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 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!
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26567 Visits: 12506
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 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

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