How is it possible to get from the input table to the output table which are described by using SQL?
Given is the input table shown below. The table includes 3 groups and each group has items that can have parent child hierarchies. An empty "parentId" means the item is a root item.
The goal is to create a new hierarchy that follows the following concept:
- Take group 0 and recursively put all group 1 root items to the leaf items of group 0
- Take group 1 and recursively put all group 2 root items to the leaf items of group 1 ... ...
This way a new tree is being created.
Input table
| id | parentId | name | group | |
|---|---|---|---|---|
| 1 | A1 | Test1 | 0 | |
| 2 | A2 | Test2 | 0 | |
| 3 | A2.1 | A2 | Test2.1 | 0 |
| 4 | A2.2 | A2 | Test2.1 | 0 |
| 5 | A3 | Test3 | 0 | |
| 6 | B1 | Test4 | 1 | |
| 7 | B1.1 | B1 | Test4.1 | 1 |
| 8 | B1.2 | B1 | Test4.2 | 1 |
| 9 | B2 | Test5 | 1 | |
| 10 | C1 | Test6 | 2 | |
| 11 | C2 | Test7 | 2 |
As you can see in the output table below, all (leaf) items of group 0 got assigned child items (the items of group 1). And group 1 got assigned children as well (the items of group 2).
As a consequence the example output shows a total depth of 5 (meaning that the maximum hierarchy level of the generated tree is 5 - see row 20 and 21 in the output).
The id is an array and consists of the ids (they serve as id but also describe the position of the item in the tree).
Output table
| id | parentId | isLeaf | level | name | |
|---|---|---|---|---|---|
| 1 | root | 0 | root | ||
| 2 | A1 | root | 1 | Test1 | |
| 3 | A1,B1 | A1 | 2 | Test4 | |
| 4 | A1,B1,B1.1 | A1,B1 | 3 | Test4.1 | |
| 5 | A1,B1,B1.1,C1 | A1,B1,B1.1 | 1 | 4 | Test6 |
| 6 | A1,B1,B1.1,C2 | A1,B1,B1.1 | 1 | 4 | Test7 |
| 7 | A1,B1,B1.2 | A1,B1 | 3 | Test4.2 | |
| 8 | A1,B1,B1.2,C1 | A1,B1,B1.2 | 1 | 4 | Test6 |
| 9 | A1,B1,B1.2,C2 | A1,B1,B1.2 | 1 | 4 | Test7 |
| 10 | A1,B2 | A1 | 2 | Test5 | |
| 11 | A1,B2,C1 | A1,B2 | 1 | 3 | Test6 |
| 12 | A1,B2,C2 | A1,B2 | 1 | 3 | Test7 |
| 13 | A2 | root | 1 | Test2 | |
| 14 | A2,A2.1 | A2 | 2 | Test2.1 | |
| 15 | A2,A2.1,B1 | A2,A2.1 | 1 | 3 | Test4 |
| 16 | A2,A2.1,B1,B1.1 | A2,A2.1,B1 | 4 | Test4.1 | |
| 17 | A2,A2.1,B1,B1.1,C1 | A2,A2.1,B1,B1.1 | 1 | 5 | Test6 |
| 18 | A2,A2.1,B1,B1.1,C2 | A2,A2.1,B1,B1.1 | 1 | 5 | Test7 |
| 19 | A2,A2.1,B1,B1.2 | A2,A2.1,B1 | 4 | Test4.2 | |
| 20 | A2,A2.1,B1,B1.2,C1 | A2,A2.1,B1,B1.2 | 1 | 5 | Test6 |
| 21 | A2,A2.1,B1,B1.2,C2 | A2,A2.1,B1,B1.2 | 1 | 5 | Test7 |
| 22 | A2,A2.1,B2 | A2,A2.1 | 3 | Test5 | |
| 23 | A2,A2.1,B2,C1 | A2,A2.1,B2 | 1 | 4 | Test6 |
| 24 | A2,A2.1,B2,C2 | A2,A2.1,B2 | 1 | 4 | Test7 |
| 25 | A2,A2.2 | A2 | 2 | Test2.1 | |
| 26 | A2,A2.2,B1 | A2,A2.2 | 3 | Test4 | |
| 27 | A2,A2.2,B1,C1 | A2,A2.2,B1 | 1 | 4 | Test6 |
| 28 | A2,A2.2,B1,C2 | A2,A2.2,B1 | 1 | 4 | Test7 |
| 29 | A2,A2.2,B2 | A2,A2.2 | 3 | Test5 | |
| 30 | A2,A2.2,B2,C1 | A2,A2.2,B2 | 1 | 4 | Test6 |
| 31 | A2,A2.2,B2,C2 | A2,A2.2,B2 | 1 | 4 | Test7 |
| 32 | A3 | root | 1 | Test3 | |
| 33 | A3,B1 | A3 | 2 | Test4 | |
| 34 | A3,B1,B1.1 | A3,B1 | 3 | Test4.1 | |
| 35 | A3,B1,B1.1,C1 | A3,B1,B1.1 | 1 | 4 | Test6 |
| 36 | A3,B1,B1.1,C2 | A3,B1,B1.1 | 1 | 4 | Test7 |
| 37 | A3,B1,B1.2 | A3,B1 | 3 | Test4.2 | |
| 38 | A3,B1,B1.2,C1 | A3,B1,B1.2 | 1 | 4 | Test6 |
| 39 | A3,B1,B1.2,C2 | A3,B1,B1.2 | 1 | 4 | Test7 |
| 40 | A3,B2 | A3 | 2 | Test5 | |
| 41 | A3,B2,C1 | A3,B2 | 1 | 3 | Test6 |
| 42 | A3,B2,C2 | A3,B2 | 1 | 3 | Test7 |
I've tested a lot with recursive queries and cross joins but couldn't get near a usable output dataset. Any help is really appreciated!
https://stackoverflow.com/questions/66927056/how-to-create-a-parent-child-structure-dynamically-using-sql April 03, 2021 at 09:46AM
没有评论:
发表评论