Flatten Hierarchies using SQL and User Defined Table Functions

[aadityanigam]

• • 
    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

aField	bField
1
2	1
3	1
4	2
5	2
6	2
7	3
8	3
9	3

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

Level 1	Level 2	Level 3
1	2	4
-	3	5
-	-	6
-	-	7
-	-	8
-	-	9

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

AFIELD	BFIELD		AFIELD		AFIELD	BFIELD	LEVEL		AFIELD	BFIELD
1	?	STEP 1->		STEP 2->				STEP 3->	1	?
2	1								2	1
3	1								3	1
4	2		4		4	2	1
5	2		5		5	2	1
6	2		6		6	2	1
7	3		7		7	3	1
8	3		8		8	3	1
9	3		9		9	3	1

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:

LEVEL1	LEVEL2	LEVEL3
1	2	4
?	3	5
?	?	6
?	?	7
?	?	8
?	?	9

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