Generate A Parent-child Hierarchy From Table With Levels Paths

I managed to transform some unreadable data in such a table. The SQL table represents a multipleparent flat hierarchy. The question is, how can I generate a normal ragged sql paren

Solution 1:

You could create a new table with the hierarchical structure, and an auto incrementing ID, like this:

create table hierarchy (
  id int not null identity (1,1) primary key,
  element varchar(100),
  parent int
);

Then you would first add the level 1 elements to it, as they have no parent:

insert into hierarchy (element, parent)
  select     distinct f.level1, null
  from       flat f;

As you now have the id values generated for these elements, you can add the next level, like this:

insert into hierarchy (element, parent)
  select     distinct f.level2, h1.id
  from       hierarchy h1
  inner join flat f
          on f.level1 = h1.element
  where      h1.parent is null;

This pattern you can repeat to the next levels:

insert into hierarchy (element, parent)
  select     distinct f.level3, h2.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level4, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level5, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join hierarchy h4
          on h4.parent = h3.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
         and f.level4 = h4.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level6, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join hierarchy h4
          on h4.parent = h3.id
  inner join hierarchy h5
          on h5.parent = h4.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
         and f.level4 = h4.element
         and f.level5 = h5.element
  where      h1.parent is null;

... etc, as far into the levels as needed.