473,231 Members | 1,907 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,231 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 7946
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: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.