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