This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(64, 71%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EN%3C/text%3E%3C/svg%3E)
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
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:
What may be simpler than above (but you have to have some column to dictate the correct order of rows)?
The solution with string_agg of course! nonomame2001, if you want a simpler solution, you will need to upgrade!
the result is not my expected, why there is id column and there is null for Val at the first row.
my table just has drink column
how to prepare by using string_agg
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(281.6, 56.00000000000001%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBZ%3C/text%3E%3C/svg%3E)
Hi,@nononame2021
The reason why you can't use string_agg is that the version is too low. This aggregation function is available after sql server2017. Why not try the writing method proposed by Viorel. I think his implementation idea is very clear. If STRING_agg is used here, it will be more complicated. Because normally used within group needs to define a column in row order, but you also proposed that you only have one column.
For your convenience, I put the grammar written by the experts above, so you can test it.
create table #test(value varchar(100))
Insert into #test values
('mike tea'), ('lemon tea'), ('miki shake')
select concat( value , ', ' + lag( value ) over (order by len( value )), ', ' + lag( value , 2) over (order by len(value )))as value
from #test
Best Regards,
Bert Zhou
my table is not fixed to 3 drink. so it can't hard code to retrieve the value in specific position.
I don't have id column and null value not allowed.
my table named T1
Mike tea
lemon tea
milk shake
please create a table like above and try
anyone help on how to prepare a SQL by function string_agg.
how table is only 1 column
mike tea
lemon tea
mike shake
mike tea
mike tea, lemon tea
mike tea, lemon tea, mike shake
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;
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.