?
Solved

Flatten Hierarchy table

Posted on 2015-01-15
7
Medium Priority
?
203 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
[X]
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
  • 3
  • 2
  • 2
7 Comments
 
LVL 38

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 38

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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

777 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