How to concatenate rows with ORDER BY

Question

Edited: In SQL, I’m joining a table with a text column to a separate table (the latter not depicted in my example below). I would like to display the text from each row concatenated as a single column in a row. Using a separate column (Line), I’d like to use to determine the order in which the text is concatenated.

Col   Line   Text
a     1      This
a     4      sentence
a     3      a
a     2      is

Output in a single column/row:

This is a sentence

Are you asking about SQL or some other language? Is it a database or in-memory table of some sort? — PM 77-1
– PM 77-1, Commented Nov 27, 2019 at 16:15
You can use ORDER BY Line to display the rows in the desired order, but it will take programming to concatenate the values of Str into a string. — Mike Robinson
– Mike Robinson, Commented Nov 27, 2019 at 16:19
Hi, it will be possible to help if you provide a language you are using and any extra details (e.g. things you have tried) — Josh
– Josh, Commented Nov 27, 2019 at 16:29
string aggregation differs depending on the DBMS. For example MySql - GROUP_CONCAT, Oracle DB - LISTAGG, Sql Server - STRING_AGG, PostgreSql - STRING_AGG. So knowing your DBMS & version might be helpfull. — LukStorms
– LukStorms, Commented Nov 27, 2019 at 17:15

David Sugar · Accepted Answer · 2019-11-27 17:46:51Z

1

In MySql try:

select  group_concat(Str order by Line separator ' ' ) as Sentence FROM Table1
group by Col;

Result:

sentence
This is a sentence

http://sqlfiddle.com/#!9/e84f9e/5

answered Nov 27, 2019 at 17:46

David Sugar

1,32610 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LukStorms Over a year ago

Rofl, 2 answers for the price of one. Nice one.;)

David Sugar · Accepted Answer · 2019-11-27 17:50:49Z

1

Try this in postgresql:

select  string_agg(Str,' ' order by Line ) as Sentence FROM Table1
group by Col;

Output:

sentence
This is a sentence

http://sqlfiddle.com/#!17/e84f9/4

edited Nov 27, 2019 at 17:50

answered Nov 27, 2019 at 17:39

David Sugar

1,32610 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to concatenate rows with ORDER BY

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related