SQL- to append accumlate text from previous row to next row within Table

Question

1 table with drink table

drink table with following drink (3 row in drink table)

mike tea
lemon tea
mike shake

---

how to prepare a SQL to list out as below:

mike tea
mike tea, lemon tea
mike tea, lemon tea, mike shake

Answer 1

anyone know?

simple example is table contain:

1
2
3

how to list out the result as below: (how to write a SQL to do that )

1
1,2
1,2,3

Answer 2

Try:

INSERT INTO @t (val)
SELECT * FROM (VALUES
    (NULL -- val - int
    ), (1), (2), (3)) x(val)

SELECT * FROM @t    

SELECT id, val, (SELECT STRING_AGG(val, ', ') WITHIN GROUP (ORDER BY id ) AS val2 FROM @t t2 WHERE t2.id <= t1.id) 
FROM @t t1

Answer 3

anyone help?

Answer 4

Try:

DECLARE @t TABLE (id INT IDENTITY(1,1) PRIMARY key, val VARCHAR(30))

INSERT INTO @t (val)
SELECT * FROM (VALUES
    (NULL -- val - int
    ), ('mike tea'), ('lemon tea'), ('miki shake')) x(val)

SELECT * FROM @t    

SELECT id, val, STUFF( (SELECT ', ' + val  FROM @t t2 WHERE t2.id <= t1.id FOR XML PATH('')),1,2,'') AS val2 
FROM @t t1

Answer 5

any other simply way to do that?

just single select?