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!

Which database are you using? – Asier Azkuenaga Feb 17 '17 at 11:35 — Asier Azkuenaga, Feb 17 '17 at 11:35
Sorry, getting use to participating in this. – Asier Azkuenaga Feb 17 '17 at 11:39 — Asier Azkuenaga, Feb 17 '17 at 11:39

score 5 · Accepted Answer · answered Feb 17 '17 at 11:51

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.

score 2 · Answer 2 · answered Feb 17 '17 at 11:35

2

This is same as concatenate columns to rows

select id,name,pid,
stuff((select '-'+name from yourtable  n2 where n2.id<=n1.id for xml path('')),1,1,'') b
from yourtable n1

answered Feb 17 '17 at 11:35

TheGameiswar

27,855
8
56
94

Insert new colum with previous rows information

2 Answers2