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

Working from Naomi's solution:

 DECLARE @t TABLE (val VARCHAR(30))

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

 SELECT * FROM @t    

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

Now, you may object that the order is not what you wanted, but there is an important lesson to learn here: There is no way you can get that exact result you ask for in a guaranteed way. You may discern an order when you look at your data, but that order is a mirage. According to the rules of relational databases, a table is an unordered object. So if there is column to hold an order like an id, all we can sort on is the drink column itself. If you want to track that lemon tea comes in between mike tea and mike shake, you must have a column to encode this. There is no alternative.

Hi @nononame2021 ,

Database tables shall have a primary key. That's why everybody assumed that you have it.

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @t TABLE (val VARCHAR(30) NOT NULL);  
      
INSERT INTO @t (val) VALUES  
('mike tea'), ('lemon tea'), ('miki shake');  
-- DDL and sample data population, end  
      
;WITH rs AS  
(  
 SELECT *   
 , id = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  
 FROM @t  
)  
SELECT STUFF( (SELECT ', ' + val    
 FROM rs t2 WHERE t2.id <= t1.id FOR XML PATH('')),1,2,'') AS Result ;  

-- SQL Server 2017 onwards  
;WITH rs AS  
(  
	SELECT *   
		, id = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  
	FROM @t  
)  
SELECT (SELECT STRING_AGG(val, ', ') WITHIN GROUP (ORDER BY id ) AS val2 FROM rs t2 WHERE t2.id <= rs.id) AS Result   
FROM rs;  

anyone help on how to prepare a SQL by function string_agg.

how table is only 1 column

val

mike tea
lemon tea
mike shake

expected output

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

What may be simpler than above (but you have to have some column to dictate the correct order of rows)?

To check version use
SELECT @@version

i got the following output: