Flatten hierarchy - Iteration on left outer join

[Anonymous]

Hello.

I have a table (from json) which have 3 columns:

id    |    description    |    parent_id

001   |        Toys       |     (NULL)

002   |      Vehicles     |       001

003   |        Cars       |       002

...   |         ...       |       ...

I need to flatten this hierarchy structure.

One way I can think of, is first doing self join on the parent_id and id, then keep doing left outer join with the original table, until the output with the last column all NULL.

The result should look like the below:

id    |  description  |  parent_id  |   parent_id_1  |   parent_id_2  |

001   |      Toys     |    (NULL)   |     (NULL)     |     (NULL)     |

002   |    Vehicles   |     001     |     (NULL)     |     (NULL)     |

003   |      Cars     |     002     |       001      |     (NULL)     |

...   |       ...     |     ...     |       ...      |       ...      | 

How can do this in Talend? Or is there any other easier way to flatten such hierarchy?

[Anonymous · Accepted solution]

I wrote this a long time ago as an experiment (https://www.rilhia.com/tutorials/talend-connect-example). It is very much along the same lines as what you are attempting. It may work, but it is tricky and was only written to see if I could do it.

The problem you have is that you need to use recursion for this and that is something Talend doesn't handle all that well. You *could* write a recursive routine to do this and return a comma separated list representing your hierarchy. After that you would split that up into different columns using a tMap. I'd probably opt for something like that if I had to do solely inside Talend.

Another way to do it would be to load the data into Oracle and carry out a connect by prior query. Of course that only works if you have the environment to do it that way. 

[prabinpatroc1]

The link you have provided is not working. Can I see the solution in any other ways ?