|
|
Posted by Erland Sommarskog on 09/21/06 22:30
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]
|