asked on

Flatten Hierarchy table

Query 1:
select Children_id Parent_id from aa
where Parent_id='2006'

Children_id Parent_id

2086 2006
2087 2006
2088 2006
2089 2006

Query 2:
select Children_id Parent_id from aa
where Children_id='2006'

Children_id Parent_id
2006 2001

Query 3:
select Children_id Parent_id from aa
where Children_id='2001'

Children_id Parent_id
2001 2000

Query 3:
select Children_id Parent_id from aa
where Children_id='2000'

Children_id Parent_id
2000 null

I want to flatten this table like below:

Lev1 Lev2 Lev3 Lev4 Lev5 Lev6

2000 2001 2006 2086
2000 2001 2006 2087
2000 2001 2006 2088
2000 2001 2006 2089

Lev 5 and 6 just copy of Lev4

Lev1 Lev2 Lev3 Lev4 Lev5 Lev6

2000 2001 2006 2086 2086 2086
2000 2001 2006 2087 2087 2087
2000 2001 2006 2088 2088 2088
2000 2001 2006 2089 2089 2089

Thanks

PatHartman

You can do this with a query but only if you know the max depth of the hierarchy. You use a self join and you add the table to the qbe for as many levels deep as you want to go. The first instance is tblA, then Access starts adding suffixes. tblA_1 (for the second instance), tblA_2 (for the third), etc.

Once you have added the table as many times as you needed to, draw the join lines.
tblA_1.FK to tblA.PK
tblA_2.FK to tbl_1.PK
tblA_3.FK to tbl_2.PK

As you select the columns you need at each level, use an alias for the name so that you can make sense out of them.
Select tblA.fld1 as fld1_lvl1, tblA_1.fld1 as fld1_lvl2, ....

sam2929

ASKER

Depth can be max 6 that's why I have level 6

PatHartman

OK, did you try to build the query I suggested? Use the QBE. It is simpler than trying to keep the parentheses straight when writing such a nested join.

Valliappan AN

Try this:

;with MyCTE as (
select parent_id, children_id, cast(children_id as varchar(max)) AS lineage, 1 as depth
from aa
where parent_id is null

union all

select t2.parent_id, t2.children_id, cast(lineage as varchar(max)) + ' ' + cast(t2.children_id as varchar(max)) AS lineage, depth+1 As depth
from MyCTE t1
inner join aa t2 on t1.children_id = t2.parent_id
)
select parent_id, children_id, lineage + REPLICATE(' '+cast(children_id as varchar), 6-depth) from MyCTE

sam2929

ASKER

i am getting results as below:

parent_id, children_id,No column
NULL 25108 25108 25108 25108 25108 25108 25108
NULL 5375 5375 5375 5375 5375 5375 5375

sam2929

ASKER

I want results as
parent_id, children_id,lev1,lev2,lev3,lev4,lev5,lev6
NULL 25108 25108 25108 25108 25108 25108 25108
NULL 5375 5375 5375 5375 5375 5375 5375

ASKER CERTIFIED SOLUTION

Valliappan AN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial