|
|
Posted by Dip on 09/22/06 04:36
WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?
Regards,
Soumya
Erland Sommarskog wrote:
> Dip (soumyadip.bhattacharya@gmail.com) writes:
> > 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!
>
> You need to retrieve the current max level, and then construct the
> query dynamically according to this. This can be done in client
> code or in T-SQL. For information about dyamic SQL from T-SQL see
> http://www.sommarskog.se/dynamic_sql.html.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|