I have a slightly more complex task for the cracks among us. I am trying to learn steadily. I have attached an excerpt from a table that a customer supplied to me.
It is a list of OP codes that he wants to have transformed for a database.
_Key_CHOP: is the unique ID per row, and is composed of the year and the CHOP_Code (I have only inserted the relevant columns).
CHOP_Code: Code for an operation/procedure, which, however, can repeat itself in the years, therefore not unique.
nbchar: denotes the number of characters per CHOP code.
Now I am faced with the challenge of mapping the table into a hierarchy structure:
As an example, I have manually displayed the first 10 rows of what the result should look like:
| _Key_CHOP | CHOP_Code | Level 1 | Level 2 | Level 3 | Level 4 | ... | ... | |
| 2022_0 | 0 | 0 | | | | | | |
| 2022_00 | 00 | 0 | 00 | | | | | |
| 2022_000 | 000 | 0 | 00 | 000 | | | | |
| 2022_0001 | 0001 | 0 | 00 | 000 | 0001 | | | |
| 2022_0002 | 0002 | 0 | 00 | 000 | 0002 | | | |
| 2022_0003 | 0003 | 0 | 00 | 000 | 0003 | | | |
| 2022_0009 | 0009 | 0 | 00 | 000 | 0009 | | | |
| 2022_001 | 001 | 0 | 00 | 001 | | | | |
| 2022_0010 | 0010 | 0 | 00 | 001 | 0010 | | | |
| 2022_0011 | 0011 | 0 | 00 | 001 | 0011 | | | |
The level structure must be dynamic, so for example in line 13 there is a fifth level.
Unfortunately, I don't have a unique parent-child structure, but the nbchar can be used for this.
Who has a speditive idea?
