Recursive CTE

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

DECLARE @t TABLE(Id INT, ParentID INT, Caption VARCHAR(10))

INSERT INTO @t(Id, ParentID, Caption)

SELECT 1, 0, 'Caption1' UNION ALL

SELECT 2, 1, 'Caption2' UNION ALL

SELECT 3, 2, 'Caption3';

WITH CTE AS (

SELECT Id, ParentID, Caption, CAST(Caption AS VARCHAR(MAX)) AS FullCaption

FROM @t

WHERE ParentID=0

UNION ALL

SELECT a.Id, a.ParentID, a.Caption, c.FullCaption + '\' + a.Caption

FROM @t a

INNER JOIN CTE c ON c.Id = a.ParentID)

SELECT Id, ParentID, Caption, FullCaption

FROM CTE;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

denny_j SSC Enthusiast Points: 171 More actions · Answer 2

SSC Enthusiast

Points: 171

October 21, 2010 at 10:53 pm

Thanks Mark..