473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Flattening Parent Child Hierarchy: Urgent please help

Dip
Hi Expert,
How do I flatten a Parent Child hierarchy to regular flat data: please
provide some SQL code:

I have now:
Task_ID, Parent_Task_ID, Task_Name Level
1 1 Project Management 1
2 1 Costing 2
3 1 Estimating 2
4 2 Task1 3
5 2 Task2 3
6 3 Task3 3
7 3 Task4 3

I want to have:

Level1 Level2 Level3
Project Management Costing Task1
Project Management Costing Task2
Project Management Estimating Task3
Project Management Estimating Task4

Please help, I am stuck!
Thanks in advance.
Soumya

Aug 10 '06 #1
2 7951
Dip wrote:
Hi Expert,
How do I flatten a Parent Child hierarchy to regular flat data: please
provide some SQL code:

I want to have:

Level1 Level2 Level3
Project Management Costing Task1
Project Management Costing Task2
Project Management Estimating Task3
Project Management Estimating Task4

Sounds pretty straightforward, joining the table into itself as many
times as you need to get the depth you want. What have you tried so
far, and what is the specific issue you're coming up against?
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

---
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/

Aug 10 '06 #2
On 10 Aug 2006 07:16:56 -0700, "Jason Kester" <ja*********@gmail.com>
wrote:
>Dip wrote:
>Hi Expert,
How do I flatten a Parent Child hierarchy to regular flat data: please
provide some SQL code:

I want to have:

Level1 Level2 Level3
Project Management Costing Task1
Project Management Costing Task2
Project Management Estimating Task3
Project Management Estimating Task4


Sounds pretty straightforward, joining the table into itself as many
times as you need to get the depth you want. What have you tried so
far, and what is the specific issue you're coming up against?
That is:

select
mt1.Task_Name Level1,
mt2.Task_Name Level2,
mt3.Task_Name Level3
from my_table mt1
join my_table mt2 on mt2.Parent_Task_ID = mt1.Task_ID
and mt2.Level = 2
join my_table mt3 on mt3.Parent_Task_ID = mt2.Task_ID
and mt3.Level = 3

If you're not guaranteed to have data at all levels, then replace the
joins with left joins.

If you don't trust Level to be accurate, but do trust all and only
first-level rows to have Parent_Task_ID = their own Task_ID, then
do this instead:

select
mt1.Task_Name Level1,
mt2.Task_Name Level2,
mt3.Task_Name Level3
from my_table mt1
join my_table mt2 on mt2.Parent_Task_ID = mt1.Task_ID
and mt1.Parent_Task_ID = mt1.Task_ID
and mt2.Parent_Task_ID <mt2.Task_ID
join my_table mt3 on mt3.Parent_Task_ID = mt2.Task_ID
Aug 11 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Paul | last post by:
Hi all, Here is what I am trying to do. I have a parent class calling a child class. when one function in child class is called, i need to call parent class' function. How can I get parent class'...
16
by: Suzanne Vogel | last post by:
Hi, I've been trying to write a function to test whether one class is derived from another class. I am given only id's of the two classes. Therefore, direct use of template methods is not an...
1
by: solex | last post by:
Hello, Part of my XML application will allow the user to select any field(s) from the hiearchy as output to a CSV file for viewing/ manipulation. The datastore consists of many XML files that...
3
by: maricel | last post by:
Is there a way to list (using db2 command or catalogs) to list hierarchy of table parent & child relationship: 1) A list that shows which table should be deleted first,second,third... 2) A list...
5
by: Patrick Vanden Driessche | last post by:
Hi All, I'm currently writing an in-house Form validation framework (WinForms) which is based on 'Component'-inheriting object. So basically, I have a small hierarchy. FormValidator +--...
8
by: Dip | last post by:
Hello Experts, Here is the code to flatten a PC hierarchy into a level based table. It works fine. SELECT t1.TASK_ID AS TASK_LV1, t2.TASK_ID AS TASK_LV2, t3.TASK_ID AS TASK_LV3, t4.TASK_ID AS...
3
by: Joe | last post by:
I need to popup a window, allow the user to select an image, update the parent window with this new value (requires a refresh) and then close the child window. Are there any examples of...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
4
by: Harlequin | last post by:
I have a question concerning the need to trigger events within a "child" subform which is itself enbedded within a master "parent" form and which is accessible via a tab in the parent form. Becuase...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.