Insert new colum with previous rows information

Question

I have this table:

   Id  |Name        |ParentId 
    1  |John        |Null     
    2  |Oscar       |1        
    3  |Peter       |2        
    4  |Abbey       |3        
    5  |Adrian      |4        
    6  |Barbara     |5    

and i want to make a select that will give me a new column that gets the previous Name with by the parentId to make a listName (Order by ParentID).

the final result in this example would be this:

   Id  |Name        |ParentId     | List
    1  |John        |Null         | John
    2  |Oscar       |1            | John-Oscar
    3  |Peter       |2            | John-Oscar-Peter
    4  |Abbey       |3            | John-Oscar-Peter-Abbey
    5  |Adrian      |4            | John-Oscar-Peter-Abbey-Adrian
    6  |Barbara     |5            | John-Oscar-Peter-Abbey-Adrian-Barbara

Thnks for all the help!

Answer 1

You can use a recursive CTE to produce the desired result:

declare @t table (Id int, Name varchar(20), ParentId int)
insert @t values
(    1  ,'John'        ,Null     ),
(    2  ,'Oscar'       ,1        ),
(    3  ,'Peter'       ,2        ),
(    4  ,'Abbey'       ,3        ),
(    5  ,'Adrian'      ,4        ),
(    6  ,'Barbara'     ,5    )

;with x as (
    select *, cast(name as varchar(1000)) as list from @t where parentid is null
    union all
    select t.id, t.name, t.parentid, cast(x.list+'-'+t.name as varchar(1000)) from @t t join x on t.parentid = x.id
)
select * from x

This also works for multiple roots, of course.