I have a table that links teams, departments and divisions together.
| Team Id | Parent_Id | Team Name | Level |
|---|---|---|---|
| 1 | 4 | UK | 2 |
| 2 | 4 | France | 2 |
| 3 | 5 | Japan | 2 |
| 4 | 6 | Europe | 1 |
| 5 | 6 | Asia | 1 |
| 6 | NULL | Global | 0 |
I would like to be able to ‘stretch’ this table into a wide view like below;
| Team Id L2 | Team Name L2 | Team Id L1 | Team Name L2 | Team Id L3 | Team Name L3 |
|---|---|---|---|---|---|
| 1 | UK | 4 | Europe | 6 | Global |
| 2 | France | 4 | Europe | 6 | Global |
| 3 | Japan | 5 | Asia | 6 | Global |
Whilst this is simply done using repeated inner joins when I know how many levels there are I would like to come up with something more generic so that the query would not need re-writing in the event that extra levels were added to the structure.
To date I have gone round in circles trying to use the pivot function which seems like the right thing to do but I just can’t get it right. Likewise I have looked into recursive CTE’s but that only seems to provide me with narrow tables instead of the wide one I am aiming for.
Data_Mauler_2024 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1