StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMS SQL server and Trees
text
Body
copied!<p>Im looking for some way of extrating data form a tree table as defined below.</p> <p>Table Tree Defined as :-<br> TreeID uniqueidentifier<br> TreeParent uniqueidentifier<br> TreeCode varchar(50)<br> TreeDesc varchar(100) </p> <p>Data some (23k rows), Parent Refs back into ID in table</p> <p>The following SQL renders the whole tree (takes arround 2 mins 30)</p> <p>I need to do the following.</p> <p>1) Render each Tree Node with its LVL 1 parent<br> 2) Render all nodes that have a Description that matches a TreeDesc like 'SomeText%'<br> 3) Render all parent nodes that are for a single tree id. </p> <p>Items 2 and 3 take 2mins30 so this needs to be a lot faster!<br> Item 1, just cant work out how to do it with out killing SQL or taking forever</p> <p>any sugestions would be helpfull</p> <p>Thanks</p> <p>Julian</p> <pre><code>WITH TreeCTE(TreeCode, TreeDesc, depth, TreeParent, TreeID) AS ( -- anchor member SELECT cast('' as varchar(50)) as TreeCode , cast('Trees' as varchar(100)) as TreeDesc, cast('0' as Integer) as depth, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) as TreeParent, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) as TreeID UNION ALL -- recursive member SELECT s.TreeCode, s.TreeDesc, cte.depth+1, isnull(s.TreeParent, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)), isnull(s.TreeID, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) FROM pdTrees AS S JOIN TreeCTE AS cte ON isnull(s.TreeParent, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) = isnull( cte.TreeID , cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) ) -- outer query SELECT s.TreeID, s.TreeCode, s.TreeDesc, s.depth, s.TreeParent FROM TreeCTE s </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload