Re: Flattening Parent Child, an issue, please help
- From: "Dip" <soumyadip.bhattacharya@xxxxxxxxx>
- Date: 13 Sep 2006 21:12:56 -0700
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@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Flattening Parent Child, an issue, please help
- From: Dip
- Re: Flattening Parent Child, an issue, please help
- From: Erland Sommarskog
- Flattening Parent Child, an issue, please help
- Prev by Date: The user does not have permission to perform this action.
- Next by Date: Re: The user does not have permission to perform this action.
- Previous by thread: Re: Flattening Parent Child, an issue, please help
- Next by thread: Recurring Scheduled Job
- Index(es):
Relevant Pages
|
