|
|
Posted by Erland Sommarskog on 09/13/06 09:11
Dip (soumyadip.bhattacharya@gmail.com) writes:
> Here is the code to flatten a PC hierarchy into a level based table. It
> works fine.
> 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
>
> How do modify the code to work for any level rather than hard coding
> the level up to "5"?
If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.
If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.
--
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]
|