I want to extract a hierarchical structure from a table in the sql server database. Table look similar to this with levels going till lvl 10:
| lvl1 | lvl2 | lvl3 |
| ------------ | ------------ | ------------ |
I want to extract a hierarchical structure from a table in the sql server database. Table look similar to this with levels going till lvl 10:
lvl1 | lvl2 | lvl3 |
---|---|---|
Oracle | Marketing unit | Internet |
Oracle | Lawyers unit | Intellectual |
Oracle | Finance unit | null |
Oracle | Lawyers unit | Judicial department |
Oracle | IT unit | Database |
Oracle | Marketing unit | Television |
Oracle | IT unit | ERP |
That's what I want to get:
Oracle
Marketing unit
Internet
Television
Lawyers unit
Intellectual
Judicial department
Finance unit
IT unit
Database
ERP
I read about SQL hierarchical queries, but I have no idea how to do that with my table structure...
I would be grateful for the help, any ideas?
I have used unpivot, the result
Oracle
-list of all level 2
-list of all level 3
Oracle | Marketing unit | Internet |
Oracle | Lawyers unit | Intellectual |
Oracle | Finance unit | null |
Oracle | Lawyers unit | Judicial department |
Oracle | IT unit | Database |
Oracle | Marketing unit | Television |
Oracle | IT unit | ERP |
3条答案
按热度按时间s3fp2yjn1#
I'm not sure what you intend to use these results for, most cases would want some sort of relationship defined, but to replicated your stated required output you could use:
All we're doing here is making seperate rows for the groups, and then sticking them back together, while comparing this row to the last row using
LAG()
.ltskdhd12#
Your structure is a little clumsy, but this would be easy to expand up to 10 levels
You may notice the siblings are in alphabetical order, if you wanted a specific order, there would need to be another column with the proper order/sequence.
Example or dbFiddle
Results
dgtucam13#
I think there may be a simpler way, unless I am missing the point. Why not simply group and sort? You can do a series of these with "go" in the middle, or just one big table:
Or do a summary queries of each level
But I might be missing the point, because this is simple. I just wanted to include it because someone gave me a simple answer once, and it was the correct one.