sql Id concatenation in sequence in a separate column like running total

Question

I need running Id concatenation just like running balance or total.. Concatenate the previous Ids to current Id row wise just like shown in picture

query is

with relation (Id, [orderSequence])  
as  
(  
select Id,cast(Id as varchar(20))  
from [ACChartofAccount]  

union all  
select p.Id, cast(Cast(r.Id as varchar) + ',' + cast(p.Id as varchar) as varchar(20))  
from [ACChartofAccount] p  
inner join relation r on p.ParentId = r.Id 
)  

select Id,orderSequence 
from relation  
order by orderSequence

Answer 1

You can use below query to get above result.

DECLARE @Table TABLE(ID VARCHAR(10));
INSERT INTO @table(ID) VALUES ('320'),(332),(333),(334),(335);
SELECT mt.ID,
    STUFF((  
        SELECT ', ' + ID 
        FROM @table t
        WHERE t.ID <= mt.ID 
        FOR XML PATH('')), 1, 2, '') AS oldersequence
FROM @table mt 
ORDER BY ID