Writing a recursive SQL query on a self-referencing table

Question

I have a database with a table called Items, that contains these columns:

• ID - primary key, uniqueidentifier
• Name - nvarchar(256)
• ParentID - uniqueidentifier

The name field can be used to build out a path to the item, by iterating through each ParentId until it equals '11111111-1111-1111-1111-111111111111', which is a root item.

So if you had a table that had rows like

ID                                   Name        ParentID
-------------------------------------------------------------------------------------
11111111-1111-1111-1111-111111111112 grandparent 11111111-1111-1111-1111-111111111111
22222222-2222-2222-2222-222222222222 parent      11111111-1111-1111-1111-111111111112
33333333-3333-3333-3333-333333333333 widget      22222222-2222-2222-2222-222222222222

So if I looked up an item with id '33333333-3333-3333-3333-333333333333' in the example above, i'd want the path

/grandparent/parent/widget 

returned. i've attempted to write a CTE, as it looks like that's how you'd normally accomplish something like this - but as I don't do very much SQL, I can't quite figure out where i'm going wrong. I've looked at some examples, and this is as close as I seem to be able to get - which only returns the child row.

declare @id uniqueidentifier
set @id = '10071886-A354-4BE6-B55C-E5DBCF633FE6'
;with ItemPath as (
    select a.[Id], a.[Name], a.ParentID 
        from Items a
            where Id = @id

    union all

    select parent.[Id], parent.[Name], parent.ParentID 
        from Items parent 
            inner join ItemPath as a
                on a.Id = parent.id
                    where parent.ParentId = a.[Id]
)
select * from ItemPath

I have no idea how i'd declare a local variable for the path and keep appending to it in the recursive query. i was going to try to at least get all the rows to the parent before going after that. if anyone could help with that as well - i'd appreciate it.

Answer 1

well here's working solution

SQL FIDDLE EXAMPLE

declare @id uniqueidentifier
set @id = '33333333-3333-3333-3333-333333333333'

;with ItemPath as 
(
    select a.[Id], a.[Name], a.ParentID 
    from Items a
    where Id = @id

    union all

    select parent.[Id], parent.[Name] + '/' + a.[Name], parent.ParentID 
    from ItemPath as a
        inner join Items as parent on parent.id = a.parentID
)
select * 
from ItemPath
where ID = '11111111-1111-1111-1111-111111111112'

I don't like it much, I think better solution will be to do it other way around. Wait a minute and I try to write another query :)

UPDATE here it is

SQL FIDDLE EXAMPLE

create view vw_Names
as
    with ItemPath as 
    (
        select a.[Id], cast(a.[Name] as nvarchar(max)) as Name, a.ParentID 
        from Items a
        where Id = '11111111-1111-1111-1111-111111111112'

        union all

        select a.[Id], parent.[Name] + '/' + a.[Name], a.ParentID 
        from Items as a
            inner join ItemPath as parent on parent.id = a.parentID
    )
select * 
from ItemPath

and now you can use this view

declare @id uniqueidentifier
set @id = '33333333-3333-3333-3333-333333333333'

select * 
from vw_Names where Id = @id