Flattening Parent Child Hierarchy: Urgent please help

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_NameLevel
11Project Management1
21Costing2
31Estimating2
42Task13
52Task23
63Task33
73Task43

I want to have:

Level1Level2Level3
Project ManagementCostingTask1
Project ManagementCostingTask2
Project ManagementEstimatingTask3
Project ManagementEstimatingTask4

Please help, I am stuck!
Thanks in advance.
SoumyaDip wrote:

Quote:

Originally Posted by

Hi Expert,
How do I flatten a Parent Child hierarchy to regular flat data: please
provide some SQL code:
>
I want to have:
>
Level1Level2Level3
Project ManagementCostingTask1
Project ManagementCostingTask2
Project ManagementEstimatingTask3
Project ManagementEstimatingTask4
>

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/|||On 10 Aug 2006 07:16:56 -0700, "Jason Kester" <jasonkester@.gmail.com>
wrote:

Quote:

Originally Posted by

>Dip wrote:

Quote:

Originally Posted by

>Hi Expert,
>How do I flatten a Parent Child hierarchy to regular flat data: please
>provide some SQL code:
>>
>I want to have:
>>
>Level1Level2Level3
>Project ManagementCostingTask1
>Project ManagementCostingTask2
>Project ManagementEstimatingTask3
>Project ManagementEstimatingTask4
>>

>
>
>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