|
|
Posted by Dip on 09/21/06 06:04
Hi Celko,
Thanks for your input.
The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!
Any help is greatly appriciated.
Thanks.
Soumya
--CELKO-- wrote:
> >> Here is the code to flatten a PC hierarchy into a level based table. <<
>
> I am not sure what a "level based table" is and you did not bother to
> post DDL. I am guessing you mean that you have an adjacency list model
> for your hierarchy.
>
> >> How do modify the code to work for any level rather than hard coding the level up to "5"? <<
>
> One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
> columns.
>
> A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
> concatenated string.
>
> The right answer is that display is done in the front end and never in
> the back end in a tiered archtiecture.
>
> You might also want to get a copy of TREES & HIERARCHIES IN SQL for
> toher ways to model these problems.
Navigation:
[Reply to this message]
|