Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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
0
sam2929
Asked:
sam2929
  • 3
  • 2
  • 2
1 Solution
 
PatHartmanCommented:
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, ....
0
 
sam2929Author Commented:
Depth can be max 6 that's why I have level 6
0
 
PatHartmanCommented:
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Valliappan ANSenior Tech ConsultantCommented:
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
0
 
sam2929Author Commented:
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
0
 
sam2929Author Commented:
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
0
 
Valliappan ANSenior Tech ConsultantCommented:
You may 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
),
MyList as (
select parent_id, children_id, lineage + REPLICATE(','+cast(children_id as varchar), 6-depth) As list from MyCTE ),
MyXMLList as (
select parent_id, children_id, CONVERT(xml, '<value>' + replace(list, ',', '</value><value>') + '</value>') AS xmlValues FROM MyList )
select parent_id, children_id,
      xmlValues.value('/value[1]','varchar(100)') as lev1,
      xmlValues.value('/value[2]','varchar(100)') as lev2,
      xmlValues.value('/value[3]','varchar(100)') as lev3,
      xmlValues.value('/value[4]','varchar(100)') as lev4,
      xmlValues.value('/value[5]','varchar(100)') as lev5,
      xmlValues.value('/value[6]','varchar(100)') as lev6
FROM  MyXMLList
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now