Hello Adam -
Here is an SP I developed to solve the problem. It does work, but has the
drawbacks that you have to decide upfront how many levels to support and it
seems kludgy to me, with all the temporary tables. Does anyone else have an
improvement?
Geoff
As
/*
This proc creates a table showing work assignments, the tasks subordinate to
them,
the subtasks under those, and so on for up to five levels. Note that each
level
needs its own SELECT statement because of the outer joins. Also, if only
one temp
table were used some rows would be updated (or some rows would contain
partial lists)
while others were inserted. That process would be awkward.
*/
/* Set up temp tables */
CREATE TABLE #wa_tree1
(report_level tinyint, parent_task int, child_a int)
CREATE TABLE #wa_tree2
(report_level tinyint, parent_task int, child_a int, child_b int)
CREATE TABLE #wa_tree3
(report_level tinyint, parent_task int, child_a int, child_b int,
child_c int)
CREATE TABLE #wa_tree4
(report_level tinyint, parent_task int,
child_a int, child_b int, child_c int, child_d int)
/* Start loading them */
INSERT INTO #wa_tree1 (report_level, parent_task, child_a)
SELECT a.report_level, a.task_id, b.task_id
FROM work_assignment a, work_assignment b
a.task_id *= b.wa_num
/* Delete rows where the parent is a child in another row */
DELETE FROM #wa_tree1 WHERE parent_task IN (SELECT a.child_a FROM #wa_tree1
a)
/* Get the top level children of the parents */
INSERT INTO #wa_tree2 (report_level, parent_task, child_a, child_b)
SELECT a.report_level, a.parent_task, a.child_a, b.task_id
FROM #wa_tree1 a, work_assignment b
WHERE a.child_a *= b.wa_num
/* etc */
INSERT INTO #wa_tree3 (report_level, parent_task, child_a, child_b,
child_c)
SELECT a.report_level, a.parent_task, a.child_a, a.child_b,
b.task_id
FROM #wa_tree2 a, work_assignment b
WHERE a.child_b *= b.wa_num
INSERT INTO #wa_tree4 (report_level, parent_task, child_a, child_b,
child_c, child_d)
SELECT a.report_level, a.parent_task, a.child_a, a.child_b,
a.child_c, b.task_id
FROM #wa_tree3 a, work_assignment b
WHERE a.child_c *= b.wa_num
SELECT report_level, parent_task, child_a, child_b, child_c, child_d
FROM #wa_tree4
ORDER BY parent_task, child_a, child_b, child_c, child_d
return (0)
Quote:>I have a table which represents a tree structure of folders. My problem is
>that I want to be able to list a folder and all it's subfolders. In Oracle
I
>can do that by using the "connect by prior...start with" clause, but I
>haven't been able to do anything similar in TSQL.
>Folder table
>FolderId ParentId Name
>1 NULL Root
>2 1 Home
>3 1 Public
>4 2 User1
>5 4 MyDocuments
>6 2 User2
>Please, if anybody have a clue on how to solve this in a single SQL
>statement or in an SP, give me clue.
>/Adam
by