|
|
Posted by Erland Sommarskog on 09/23/06 19:59
Dip (soumyadip.bhattacharya@gmail.com) writes:
> 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?
Did you even look at the article I posted the link to?
What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.
It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)
I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.
What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
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]
|