Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Flatten Hierarchy table

Posted on 2015-01-15
7
Medium Priority
?
219 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 39

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 39

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 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