471,601 Members | 1,687 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,601 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 7825
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Paul | last post: by
16 posts views Thread by Suzanne Vogel | last post: by
1 post views Thread by solex | last post: by
3 posts views Thread by Joe | last post: by
1 post views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by MichaelMortimer | last post: by

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.