# Flatten Hierarchies using SQL and User Defined Table Functions

#### 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;
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

### Assigned Tags

1 Comment
You must be Logged on to comment or reply to a post.

Nicely explained by breakingup the logic into sub-parts.

Q1) Have completed Function creation similar to above.

similar to above. Create Function executed fine.

Next, when trying to execute select statement : (SQL Statement mentioned below), it gives below error:

Any Idea?

Error:

Could not execute 'select three."aField" as level1, two."aField" as level2, one."aField" as level3 from (select ...' in 6.027 seconds .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] executor: plan operation failed;CalculationNode (\$\$_SYS_SS2_RETURN_VAR_\$\$) -> operation (CustomLOp):Compilation failed; CompileServerNotFoundException: No details;

Q2 ) Does this logic mentioned above working for Dynamic scenario wherein the hierarchy levels keep changing (let say: for Cost Center hierarchy the levels can be 10 and for a different hierarchy: Sales Hierarchy the levels can be 15).

Does it work for all these scenarios?

SELECT Statement for above SQL error:

=============================

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 "EDU"."HIER_FLAT"()

Where level =1) one

full outer join

(select "aField" , ROW_NUMBER() over (order by "aField") as rnum from "EDU"."HIER_FLAT"()

Where level =2) two

on one.rnum = two.rnum

full outer join

(select "aField" , ROW_NUMBER() over (order by "aField") as rnum from "EDU"."HIER_FLAT"()

Where level =3) three

on one.rnum = three.rnum

Thanks,

AK