Hi, I have a dataset in the following structure, reflecting IDs of Parent Legal entities in one column, with the IDs of their Subsidiary Legal entities in a second (Subs). Some Parents have zero Subs.
| Parent | Subs |
| 654 | - |
| 843 | 186; 175; 325; 211 |
| 184 | - |
| 746 | 514; 851; 222 |
| 567 | 117 |
| 962 | - |
I want to convert that into a set where the first column indicates all the IDs )Parents + Subs) and then the second column what the parent entity is, or, if it is a parent entity itself "Parent". Like this:
| ID | Parent ID |
| 654 | Parent |
| 843 | Parent |
| 186 | 843 |
| 175 | 843 |
| 325 | 843 |
| 211 | 843 |
| 184 | Parent |
| 746 | Parent |
| 514 | 746 |
| 851 | 746 |
| 222 | 746 |
| 567 | Parent |
| 117 | 567 |
| 962 | Parent |
It requires splitting the Subs column by delimiter, then unpivot, but also adding a record for the Parent itself. I could not figure out the last step...anybody who can help?
