Complex parent-child hierarchy question with transformation needed

[Infrecon]

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?

[Christina_H · Accepted solution]

This should be dynamic to however many levels of code you have, I added an extra code with 5 levels and it still worked.

[Infrecon]

Further additions, unfortunately my input, has not been as linear as in the example:

The following input

1 C0
2 Z00
3 Z000
4 Z0001
4 Z0002
4 Z0003
4 Z0009
3 Z001
4 Z0010
4 Z0011
4 Z0012
5 Z00120
6 Z001200
6 Z001201
6 Z001202
4 Z0016
4 Z0017
4 Z0019
3 Z002
4 Z0021

and following output needed:

1 C0
2 Z00 C0
3 Z000 Z00
4 Z0001 Z000
4 Z0002 Z000
4 Z0003 Z000
4 Z0009 Z000
3 Z001 Z00
4 Z0010 Z001
4 Z0011 Z001
4 Z0012 Z001
5 Z00120 Z0012
6 Z001200 Z00120
6 Z001201 Z00120
6 Z001202 Z00120
4 Z0016 Z001
4 Z0017 Z001
4 Z0019 Z001
3 Z002 Z00
4 Z0021 Z002

Just have a look at the attachement