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
LVL 1
Carlos Trujillo AlmeidaAdministrador de SistemasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
how big a value can  id_nivel contain ?

I take it, that id-nivel is used to determine the value of the corresponding column until a replacement value for that column is found.

Which also means that id_tab in the source bares no resemblance to id_tab in the transformed output....

Will there always be an id_nivel of  1,2,3,4 before other values appear ?

it is a very involved question and probably best solved by a cursor and populating a #tmp table....
if object_id('tempdb..#tmp_table','U') is not null drop table #tmp_table

create table #tmp_table 
(id_tab int identity
,id_nivel_1 int
,cod_nivel_1 varchar(100)
,des_nivel_1 varchar(100)
,id_nivel_2 int
,cod_nivel_2 varchar(100)
,des_nivel_2 varchar(100)
,id_nivel_3 int
,cod_nivel_3 varchar(100)
,des_nivel_3 varchar(100)
,id_nivel_4 int
,cod_nivel_4 varchar(100)
,des_nivel_4 varchar(100))

    
declare @id_nivel int
declare @cod_nivel varchar(100)
declare @des_nivel varchar(100)
declare @id_nivel_1 int
declare @cod_nivel_1 varchar(100)
declare @des_nivel_1 varchar(100)
declare @id_nivel_2 int
declare @cod_nivel_2 varchar(100)
declare @des_nivel_2 varchar(100)
declare @id_nivel_3 int
declare @cod_nivel_3 varchar(100)
declare @des_nivel_3 varchar(100)
declare @id_nivel_4 int
declare @cod_nivel_4 varchar(100)
declare @des_nivel_4 varchar(100)
declare @count int = 0

declare csr cursor for select id_nivel,cod_nivel,des_nivel
                       from yourtable

OPEN csr
FETCH NEXT FROM csr INTO @id_nivel,@cod_nivel,@des_nivel
WHILE @@FETCH_STATUS = 0
BEGIN

   set @count = @count + 1

   if @id_nivel = 1
   begin
      set @id_nivel_1 = @id_nivel
	  set @cod_nivel_1 = @cod_nivel
	  set @des_nivel_1 = @des_nivel
   end

   if @id_nivel = 2
   begin
      set @id_nivel_2 = @id_nivel
	  set @cod_nivel_2 = @cod_nivel
	  set @des_nivel_2 = @des_nivel
   end

   if @id_nivel = 3
   begin
      set @id_nivel_3 = @id_nivel
	  set @cod_nivel_3 = @cod_nivel
	  set @des_nivel_3 = @des_nivel
   end

   if @id_nivel = 4
   begin
      set @id_nivel_4 = @id_nivel
	  set @cod_nivel_4 = @cod_nivel
	  set @des_nivel_4 = @des_nivel
   end


   if @id_nivel = 4
   begin
   insert #tmp_table values (@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)
	end
							 
	FETCH NEXT FROM csr INTO @id_nivel,@cod_nivel,@des_nivel

end

CLOSE csr
DEALLOCATE csr

select * from #tmp_table

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Carlos Trujillo AlmeidaAdministrador de SistemasAuthor Commented:
Mark, thanks for you help
I answer you in order
  1. the id_level can reach up to 4 (1, 2, 3, 4)
  2. the id_level would be used to determine the number of nesting levels for the hierarchy
  3. That's right!
  4. yes, there will always be a value for id_level
  5. 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 WillsTopic AdvisorCommented:
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

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Carlos Trujillo AlmeidaAdministrador de SistemasAuthor Commented:
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 WillsTopic AdvisorCommented:
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 AlmeidaAdministrador de SistemasAuthor Commented:
Hey Mark, a thousand apologies, I hope that's fine.

Again thank you very much
Mark WillsTopic AdvisorCommented:
A pleasure, and thank you very much. Much appreciated :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.