I am trying to get recursive data.
Following code returns all parents on the top and then the children.
I would like to get data Parent 1 – his children then parent 2 - his children then parent3 – his children.
How do I do this?
USE Subscriber
GO
WITH Parent (ParentId, Id, Name,subscriberID)
AS
(
-- Anchor member definition
SELECT A.ParentId,A.id, A.name,A.SubscriberId
FROM Subscriber.Budget.SubscriberCategory AS A
WHERE ParentId IS NULL
UNION ALL
-- Recursive member definition
SELECT B.ParentId, B.id, B.name,B.SubscriberId
FROM Subscriber.Budget.SubscriberCategory AS B
INNER JOIN Parent AS P
ON B.ParentId = P.Id
)
-- Statement that executes the CTE
SELECT parentId, id, name
FROM Parent
where subscriberID = '1C18093B-5031-42E4-9251-CEF69114365F'
GO
