Flatten Hierarchies using SQL and User Defined Table Functions

• • Problem statement
  • Solution Explained
    • The solution has two parts
    • Part 1: Tagging each row with level
    • Part 2: Use the level to ‘pivot’ the table on its side
  • The code:
  • Similar Solutions:

Problem statement

Flattening of hierarchy solution that i found on SCN either required modelling or use of MDX client on a Attribute View with hierarchy.

Somehow there seemed a way for a simpler low tech solution. So here is my attempt at attacking the problem with only SQL and SQL Script.

Data looks like this

the output needs to look like this (displaying onlyaField values) :

Solution Explained

The solution has two parts

1. Tag each row with the ‘level’ it is on – we will use a User Defined Table function for this
2. Use the level to ‘pivot’ the table on its side – We will use SQL Query with table views

Part 1: Tagging each row with level

1. Identify the rows whose ‘aField’ value never occurs in ‘bField’ (for e.g. values 4 to 9 never occur in bField)
2. Row identified in step 1 are tagged with a  level (for e.g. 1)
3. Once tagged, remove the rows from the reckoning (for e.g. remove rows where aField value between 4 to 9, leaving rows with values 1..3 in aField)
4. loop over steps 1 to 3 until bField has only null values remaining. (for e.g. row 1)
5. Union all tagged rows and return

Output

Part 2: Use the level to ‘pivot’ the table on its side

1. We generate on (ad hoc) view per level column in the output filtered on level
2. Each view also uses a window function to generate rownumber
3. Using row numbers write an outer join to generate the output

Output:

The code:

FUNCTION "************"."***********.rootpkg.procs.dml::connectby" ( )
  RETURNS TABLE(aField tinyint,bfield tinyint, level tinyint)
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER AS
BEGIN
  DECLARE Rank tinyint :=0;
  DECLARE level tinyint :=1;
  DECLARE I Tinyint;
-- get the data to process
  bothFields = select aField,bField from  "DEV_1X28Q7TC9RQSNZH49YABNQ4JZ"."FLATSTRUCT";
--create the output stucture
  outputTable = select aField,bField, :level as level from :bothFields where 1=2;
  -- check if we need to an iteration
  select count(*)
  into Rank
  from (select distinct bField from :bothFields where bField is not null)
  where bField is not null;
  --get distinct superior field and count of valid values
  while :Rank > 0 Do
  -- Get the lowest level - not in superior field and tag with level id
  lowestlevel = Select aField, :level as level
  from(select aField from :bothFields
    EXCEPT
    select distinct bField from :bothFields);
  -- get rows that were not in the lowest level
  newAField = select aField from :bothFields
  EXCEPT
  select aField from :lowestlevel;
  --add tagged levels to output
  tempOut=CE_JOIN(:lowestlevel,:bothFields,["AFIELD"],["AFIELD","BFIELD","LEVEL"]);
  outputTable = Select aField, bField,  :level as level from :tempOut
    UNION ALL
    Select aField, bField, level from :outputTable;
  --remove the rows tagged with level id from data set
  bothFields = CE_JOIN(:newAField,:bothFields,["AFIELD"],["AFIELD","BFIELD"]);
  --reset level id
  level := :level+1;
  -- check if we need to do another iteration
  select count(*)
  into Rank
  from (select distinct bField from :bothFields where bField is not null)
  where bField is not null;
  END WHILE;
  --attaching the top most level
  outputTable = Select aField, bField, :level as level from :bothFields WHERE bField is null
    UNION ALL
    Select aField, bField, level from :outputTable;
  return select * from :outputTable;
END;

The SQL Query

select three.afield  as level1, two.AFIELD as level2, one.AFIELD as level3
from
(select afield, ROW_NUMBER() over (order by afield) as rnum from "DEV_1X28Q7TC9RQSNZH49YABNQ4JZ"."i036632sapdev.rootpkg.procs.dml::connectby"()
Where level =1) one
full outer join
(select afield , ROW_NUMBER() over (order by afield) as rnum from "DEV_1X28Q7TC9RQSNZH49YABNQ4JZ"."i036632sapdev.rootpkg.procs.dml::connectby"()
Where level =2) two
on one.rnum = two.rnum
full outer join
(select afield , ROW_NUMBER() over (order by afield) as rnum from "DEV_1X28Q7TC9RQSNZH49YABNQ4JZ"."i036632sapdev.rootpkg.procs.dml::connectby"()
Where level =3) three
on one.rnum = three.rnum

Similar Solutions:

Please have a look at similar solutions Flattening the hierarchy using SQL code

Request:

This is my first blog post, please do leave feedback in the comments

Edit:

Changed the heading as it did not really reflect what this blog post actually does