|
|
Posted by Ed Murphy on 08/11/06 03:09
On 10 Aug 2006 07:16:56 -0700, "Jason Kester" <jasonkester@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
Navigation:
[Reply to this message]
|