question

nononame2021-4205 avatar image
0 Votes"
nononame2021-4205 asked ErlandSommarskog answered

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

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

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@nononame2021-4205,

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

0 Votes 0 ·
nononame2021-4205 avatar image
0 Votes"
nononame2021-4205 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN edited

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
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

i got error


Msg 195, Level 15, State 10, Line 88
'STRING_AGG' is not a recognized built-in function name.

0 Votes 0 ·
NaomiNNN avatar image NaomiNNN nononame2021-4205 ·

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.

0 Votes 0 ·

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

please note the table only has a column

0 Votes 0 ·

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?

0 Votes 0 ·

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

0 Votes 0 ·

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

0 Votes 0 ·
NaomiNNN avatar image NaomiNNN nononame2021-4205 ·

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?

0 Votes 0 ·
nononame2021-4205 avatar image
0 Votes"
nononame2021-4205 answered OlafHelper-2800 commented

anyone help?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered nononame2021-4205 commented

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
nononame2021-4205 avatar image
0 Votes"
nononame2021-4205 answered nononame2021-4205 commented

any other simply way to do that?

just single select?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


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

0 Votes 0 ·

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

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered nononame2021-4205 commented

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:


188810-image.png



image.png (8.0 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!

0 Votes 0 ·

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

0 Votes 0 ·

Hi,@nononame2021-4205
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


0 Votes 0 ·

my table is not fixed to 3 drink. so it can't hard code to retrieve the value in specific position.

0 Votes 0 ·

I don't have id column and null value not allowed.


my table named T1

val_col


Mike tea
lemon tea
milk shake


please create a table like above and try

0 Votes 0 ·
nononame2021-4205 avatar image
0 Votes"
nononame2021-4205 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @nononame2021-4205,

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.