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

Question

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

nononame2021 261

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

Yitzhak Khabinsky 27,096 Reputation points

2022-04-01T12:27:27.97+00:00

@nononame2021 ,

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;)

9 answers

Your answer

Yitzhak Khabinsky 27,096 Reputation points

2022-04-01T12:27:27.97+00:00

@nononame2021 ,

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;)

Answer 1

nononame2021 261

any other simply way to do that?

just single select?

Viorel 126.3K

If there are three rows only, then:

select concat(drink, ', ' + lag(drink) over (order by len(drink)), ', ' + lag(drink, 2) over (order by len(drink)))
from DrinkTable

-- or:

select concat_ws(', ', drink, lag(drink) over (order by len(drink)), lag(drink, 2) over (order by len(drink)))
from DrinkTable

nononame2021 261 Reputation points

2022-04-01T12:51:54.52+00:00

sorry, there is 1 col only for my case, and no null allowed. only store drink value.

how to prepare a sql using sting_agg function?

only 1 column not null field in my table

Answer 2

Naomi Nosonovsky 8,881

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

nononame2021 261 Reputation points

2022-04-01T04:37:44.557+00:00

sorry, my table doesn't has id column, if there is only 'drink' column, how to.write a sql using string_agg function?

Answer 3

nononame2021 261

anyone help?

Olaf Helper 47,611 Reputation points

2022-04-01T05:35:42.353+00:00

The expected result don't make much sense and if something don't make much sense, then there is rarely an easy solution available.

Answer 4

Naomi Nosonovsky 8,881

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

nononame2021 261 Reputation points

2022-03-31T16:20:45.567+00:00

sorry, would you give me this example: and i need 1 SQL to do that, can i?

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
nononame2021 261 Reputation points

2022-03-31T16:25:13.28+00:00

i got error

Msg 195, Level 15, State 10, Line 88
'STRING_AGG' is not a recognized built-in function name.
Naomi Nosonovsky 8,881 Reputation points

2022-03-31T16:58:19.187+00:00

What is your SQL Server version? For SQL Server version less than SQL Server 2017, you would need to use FOR XML PATH('') approach, but the idea will be the same.
nononame2021 261 Reputation points

2022-03-31T17:04:46.32+00:00

I am using sql server 2016 , anyway ,would you provide the screen captured of your result.

please note the table only has a column
nononame2021 261 Reputation points

2022-03-31T17:08:18.887+00:00

sorry, to correct my sql server version.
I am not sure for the version, I am using sql server management studio v17.9

but I got error for STRING_AGG.

may I know how to check version?
nononame2021 261 Reputation points

2022-04-01T04:31:06.62+00:00

would you provide the screen capture of using string_agg result by using your script provided as my version not supported string_agg
nononame2021 261 Reputation points

2022-04-01T10:24:40.21+00:00

Hi NaomiNNN,

from your SQL above, it seems you are using STRING_AGG, but there are 2 column "id" and "val". for my case, my table only has "val" column and don't has "id" column. if that, how to prepare a SQL using STRING_AGG function to get my expected result below?

the following is recap from my first post.

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

i try your script to create table, why there is null at the first row.
id val
1 NULL
2 mike tea
3 lemon tea
4 miki shake

my table is as below.

val

mike tea
lemon tea
mike shake

how to prepare a SQL by using string_agg to output below:
mike tea
mike tea, lemon tea
mike tea, lemon tea, mike shake
Naomi Nosonovsky 8,881 Reputation points

2022-04-01T15:54:31.02+00:00

It is because I wanted to test my solution with some extra input such as NULL. You can remove that if it never going to appear in your actual data. If you only have val column and no other columns, then what exactly dictates the order of output or is it random?

Can you play with what I already showed to see if you can get the desired output?

Answer 5

nononame2021 261

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

Share via

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

9 answers

from your SQL above, it seems you are using STRING_AGG, but there are 2 column "id" and "val". for my case, my table only has "val" column and don't has "id" column. if that, how to prepare a SQL using STRING_AGG function to get my expected result below?

the following is recap from my first post.

val

Your answer