asked on

Hierarchical transformation with sql server

ejemplo.xlsxHi, experts

I need your help with a sql server programming theme, specifically with the transformation of flat records into a hierarchical structure.

I have this table with the following information

id_tab      id_nivel      cod_nivel      des_nivel
1      0      TOT SEL      SELECTED TOTAL
2      1      16      G01C0+A2 (DIVANON)
3      2      G01B0      ANTIFUNGICOS GINECOLOG
4      3      CLOMA      CLOMAZOL 3 ECU
5      4      01409      Manta
6      4      03252      Santo Domingo
7      4      01307      Babahoyo
8      4      01242      G:J.Cornel,Quito,Urdanet,Boyac
9      4      03502      Esmeraldas
10      4      01200      G: Urb. 12 de Octubre
11      3      VAGIL      VAGIL C ROWE RWE
12      4      01409      Manta
13      4      03252      Santo Domingo
14      4      01242      G:J.Cornel,Quito,Urdanet,Boyac
15      4      02104      C:Las Americas,CC.Vega,Vinueza
16      4      01523      M:Machala(sur) 25 Jun.y Palmer
17      4      01614      Milagro
.......

and I need to transform it into this

id_tab      id_nivel_1      cod_nivel_1      des_nivel_1      id_nivel_2      cod_nivel_2      des_nivel_2      id_nivel_3      cod_nivel_3      des_nivel_3      id_nivel_4      cod_nivel_4      des_nivel_4
1      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      CLOMA      CLOMAZOL 3 ECU      4      01409      Manta
2      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      CLOMA      CLOMAZOL 3 ECU      4      03252      Santo Domingo
3      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      CLOMA      CLOMAZOL 3 ECU      4      01307      Babahoyo
4      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      CLOMA      CLOMAZOL 3 ECU      4      01242      G:J.Cornel,Quito,Urdanet,Boyac
5      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      CLOMA      CLOMAZOL 3 ECU      4      03502      Esmeraldas
6      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      CLOMA      CLOMAZOL 3 ECU      4      01200      G: Urb. 12 de Octubre
7      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      VAGIL      VAGIL C ROWE RWE      4      01409      Manta
8      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      VAGIL      VAGIL C ROWE RWE      4      03252      Santo Domingo
9      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      VAGIL      VAGIL C ROWE RWE      4      01242      G:J.Cornel,Quito,Urdanet,Boyac
10      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      VAGIL      VAGIL C ROWE RWE      4      02104      C:Las Americas,CC.Vega,Vinueza
11      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      VAGIL      VAGIL C ROWE RWE      4      01523      M:Machala(sur) 25 Jun.y Palmer
12      1      16      G01C0+A2 (DIVANON)      2      G01B0      ANTIFUNGICOS GINECOLOG      3      VAGIL      VAGIL C ROWE RWE      4      01614      Milagro

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carlos Trujillo Almeida

ASKER

Mark, thanks for you help
I answer you in order

the id_level can reach up to 4 (1, 2, 3, 4)
the id_level would be used to determine the number of nesting levels for the hierarchy
That's right!
yes, there will always be a value for id_level
I was also thinking about a solution with cursors and temporary tables

For the moment the solution that you indicate is giving me a set of equal values for levels 1, 2 and 3, the only one that changes is 4.

Mark Wills

3 should also be changing.... certainly does in my testing at id_tab 6

id_tab	id_nivel_1	cod_nivel_1	des_nivel_1	id_nivel_2	cod_nivel_2	des_nivel_2	id_nivel_3	cod_nivel_3	des_nivel_3	id_nivel_4	cod_nivel_4	des_nivel_4
1	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	CLOMA	CLOMAZOL 3 ECU	4	01409	Manta
2	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	CLOMA	CLOMAZOL 3 ECU	4	03252	Santo Domingo
3	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	CLOMA	CLOMAZOL 3 ECU	4	01307	Babahoyo
4	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	CLOMA	CLOMAZOL 3 ECU	4	01242	G:J.Cornel,Quito,Urdanet,Boyac
5	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	CLOMA	CLOMAZOL 3 ECU	4	03502	Esmeraldas
6	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	CLOMA	CLOMAZOL 3 ECU	4	01200	G: Urb. 12 de Octubre
7	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	VAGIL	VAGIL C ROWERWE	4	01409	Manta
8	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	VAGIL	VAGIL C ROWERWE	4	03252	Santo Domingo
9	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	VAGIL	VAGIL C ROWERWE	4	01242	G:J.Cornel,Quito,Urdanet,Boyac
10	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	VAGIL	VAGIL C ROWERWE	4	02104	C:Las Americas,CC.Vega,Vinueza
11	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	VAGIL	VAGIL C ROWERWE	4	01523	M:Machala(sur) 25 Jun.y Palmer
12	1	16	G01C0+A2 (DIVANON)	2	G01B0	ANTIFUNGICOS GINECOLOG	3	VAGIL	VAGIL C ROWERWE	4	01614	Milagro

Open in new window

Carlos Trujillo Almeida

ASKER

Hey Mark, make a small change in the script, increase an order by in the cursor selection and the result is as expected

Thank you very much for your help and for your time

I'm going to use it, increasing some more programming logic

Mark Wills

Phew, thats a relief, been looking at it wondering why group 3 wasnt changing for you.

You have selected your own comment as the selected solution (which you can do) was hoping you might have selected my response (at least as assisted)

So, I will object to the current closure and you can then select again (hoping it was a slip of the button)

Cheers,
Mark

Carlos Trujillo Almeida

ASKER

Hey Mark, a thousand apologies, I hope that's fine.

Again thank you very much

Mark Wills

A pleasure, and thank you very much. Much appreciated :)