Solved

Flatten Hierarchy table

Posted on 2015-01-15
7
171 Views
Last Modified: 2015-02-02
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
Comment
Question by:sam2929
  • 3
  • 2
  • 2
7 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40551972
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
 

Author Comment

by:sam2929
ID: 40552178
Depth can be max 6 that's why I have level 6
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40552246
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40554052
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
 

Author Comment

by:sam2929
ID: 40557895
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
 

Author Comment

by:sam2929
ID: 40557971
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
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 500 total points
ID: 40558595
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now