469,160 Members | 1,915 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,160 developers. It's quick & easy.

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 7730
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 CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.