Posted by Dip on 09/14/06 04:12

Hello,
I was wondering whether anyone has any sample "Dynamic SQL Code" that I
can use to resolve this issues.
Thanks for any help.
Regards,
Soumya


Erland Sommarskog wrote:
> 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

• Next in forum: Re: The user does not have permission to perform this action.
• Prev in forum: The user does not have permission to perform this action.
• Thread view: Re: Flattening Parent Child, an issue, please help

[Reply to this message]