Matillion pattern to flatten a parent-child hierarchy?
What's the best pattern y'all have come up with, in Matillion, for "flattening" a parent/child (AKA self-referencing) hierarchy? In a way that produces results like this:
I've started some ideas for a Matillion transformation job. But I bet it's been done already, so I'm wondering (a) if anyone could share the best/most efficient way they've come up with?
I'm also wondering (b) whether you can set up a series of jobs...probably using Matillion variables and a Loop Iterator component...that will dynamically determine the maximum hierarchy depth N, and automatically add exactly N-1 columns (one for each non-leaf level)? So that you don't have to specify the maximum hierarchy depth?
I am going to start working on this now, but please let me know if you can share a solution for this. Thanks in advance!