I'm doing a dependency mapping explosion (like parts explosion).

I did a typical recursive CTE with a union all.

It looks like

with CTE as

( select abc from myTable where start_point = X
union all
select abc from CTE join myTable where myTable.parent = CTE.child
select * from CTE

... However this ends up with a list like

Root -> Child 1
Root -> Child 2
Root -> Child 3
Child 1-> 1 Grandchild 1
Child 2 -> 2 Grandchild 1
Child 2 -> 2 Grandchild 2

I'd prefer it looked like

Root -> Child 1 > Grandchild 1
Root -> Child 2 - >2 Grandchild 1
Root -> Child 2 -> 2 Grandchild 2
Root -> Child 3

It's like ... I need a recursive join, not a recursive union -- but when I replace the union with a join, I can't quite get it to work. Any ideas?

  • Hmm on further thought -- I think the 'joins' laterally (columns) instead of rows is not really a design pattern because --- system wouldn't know what to name the additional columns --- I think it might have to be done manually, maybe explode out (aka write N joins) 4-5 levels/ layers and manually input the new column names. So it would be Parent -> Child -> Child2 -> Child 3. There could be an alternative where you just take iterative paths but only take the 'endpoint' paths with some somewhat clever logic ... but yeah
    – user45867
    Commented Mar 15 at 0:21
  • Yea what you're looking for is not something that necessarily makes sense to have an automated operator for, because as you noted, the columns aren't static and need to be handled appropriately. What you can typically do though is use some kind of PIVOT to transform your results of the recursive CTE into a columnar format akin to your goal.
    – J.D.
    Commented Mar 15 at 2:12
  • Hierarchical data looks awkward in tabular form to begin with. When the number of levels in the hierarchy is unknown and you want each level in its own column, that's beyond awkward. That's painful.
    – Andriy M
    Commented Mar 15 at 2:38
  • It looks like You show 1 output column whereas your this ends up with a list like contains 2 columns... fix this contradiction. I'd prefer it looked like You want to select fully qualified path? if so then you must build it in recursive part using parent path and current name concatenation. Read Tips for asking a good Structured Query Language (SQL) question and provide simplified (but adequate) sample (source data and desired output) according #5 and #3.
    – Akina
    Commented Mar 15 at 4:41


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.