Flattening Parent Child, an issue, please help

[Dip]

Hello Experts,
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"?
Please help.
Thanks.
Soumya

[Erland Sommarskog]

Dip (so********************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dip]

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 (so********************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>Here is the code to flatten a PC hierarchy into a level based table. <<

I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.

>How do modify the code to work for any level rather than hard coding the level up to "5"? <<

One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.

A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.

The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.

You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.

[Dip]

Hi Celko,
Thanks for your input.
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!
Any help is greatly appriciated.
Thanks.
Soumya
--CELKO-- wrote:

Here is the code to flatten a PC hierarchy into a level based table. <<

I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.

How do modify the code to work for any level rather than hard coding the level up to "5"? <<

One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.

A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.

The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.

You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.

[Erland Sommarskog]

Dip (so********************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dip]

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?
Regards,
Soumya
Erland Sommarskog wrote:

Dip (so********************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Dip (so********************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dip]

Thanks Erland,
I actually printed out your article and went through it. It is actually
very well written and covers all general situations, however, I
didn't have much luck constructing the Dynamic SQL to generate
"possible" columns and add each "LEFT OUTER JOIN" for each
level. Even if I break it down to two SQL Text, I would still need to
tell it to add 10 columns for each level for example and 9 LEFT OUTER
JOINs to break the Parent Child Adjacent model if WBS_LEVEL is 10 for
instance.

I have designed Stored Proc with Dynamic SQL in it but I haven't done
anything like this one before. Either it is silly simple or I just
can't get my head around to it.

I don't think any literature would help me to solve this problem but
some actual code that relates to this issue.
Thanks for all help.
Soumya

Erland Sommarskog wrote:

Dip (so********************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx