Combine selected columns of multiple rows into one string

Question

Say I have the following table:

table xyz

+------+--------+--------+
| id   | field1 | field2 |
+------+--------+--------+
|    3 | ABC    | 123    |
|    4 | GHI    | 432    |
|    5 | NULL   | 444    |
+------+--------+--------+

To concatenate selected columns (field1 and field2) I can use the following query:

select coalesce(field1, '') || ' ' || coalesce(field2::text, '') from xyz;

This gives the following result:

ABC 123
GHI 432
222

How do I merge all the result rows to one row? I'd like to achieve the following

ABC 123, GHI 432, 444

SQL Fiddle

ScaisEdge · Accepted Answer · 2019-02-21T20:43:07.443

0

You could use array_agg

SELECT array_agg(coalesce(field1, '') || ' ' || coalesce(field2::text, '') )
FROM xyz

http://sqlfiddle.com/#!17/535f6/7

edited Feb 21 '19 at 20:43

answered Feb 21 '19 at 20:36

ScaisEdge

131,976
10
91
107

`string_agg()` would be more approriate – Feb 21 '19 at 20:40
@a_horse_with_no_name using string_agg is sqlfiddle don't work .. – ScaisEdge Feb 21 '19 at 20:42
Of course it does: http://sqlfiddle.com/#!17/c7d42/1 – Feb 21 '19 at 20:43
I see nothings in sqlfiddle – ScaisEdge Feb 21 '19 at 20:44
http://sqlfiddle.com/#!17/535f6/11 – Feb 21 '19 at 20:45
same .. as the above – ScaisEdge Feb 21 '19 at 20:45
sqlfiddle is extremely unreliable anyway. Use [this](https://rextester.com/OPEGX77601) or [this](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=9cb6c444407966a30d82c42f7f5174e4) – Feb 21 '19 at 20:47
@a_horse_with_no_name tanks for useful .. links – ScaisEdge Feb 21 '19 at 20:48
@a_horse_with_no_name thanks for your solution! – menteith Feb 21 '19 at 20:58

Combine selected columns of multiple rows into one string

1 Answers1