> I have data in a table like this:
>
> ID ParentID Name
> 1 0 Top
> 2 1 Level1
> 3 1 Level2
> 4 2 Child1 Of Level2
> 5 2 Child2 Of Level2
> 6 3 Child1 Of Level2
>
> So the tree would look like this:
>
> Top
> Level1
> Child1 Of Level1
> Child2 Of Level1
> Level2
> Child1 Of Level2
>
> Hopefully the tabs in the email will format when I send this so it is
> easy to read.
>
> How do I loop over this data to display the tree dynamically? I have
> used Loops with queries to get each levels childs, but the way I wrote
> it I have to have a loop for each level. What if I have unlimited
> childs? My loops will only display as many childs as I have loops.
>
> Maybe there is a better way to structure the data in the first place.
>
> Thanks!
If you are using SLQ Server 2005 or higher, use can use recursive queries using
CTEs. There are several examples out there. The one I use is like the
following.
WITH TreeCTE(cte_code, cte_name, cte_parent, cte_level, cte_sortCol)
AS
(
SELECT code, name, parent, 0,
CAST(name + ' - ' + CAST(code AS VARCHAR(10)) AS
VARBINARY(900))
FROM TreeNodes
WHERE parent IS NULL
GROUP BY code, name, parent
UNION ALL
SELECT code, name, parent, cte_level + 1,
CAST(cte_sortCol + CAST(name + ' - ' + CAST(code AS
VARCHAR(10)) AS VARBINARY(900)) AS VARBINARY(900))
FROM TreeNodes
INNER JOIN
TreeCTE
ON parent = cte_code
)
SELECT cte_code AS code, cte_name AS name, cte_level
FROM TreeCTE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320094
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
