Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Flatten Hierarchy table

Posted on 2015-01-15
7
178 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 35

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 35

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

840 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