Search code examples
sqlpostgresqlpostgresql-9.5string-aggregation

Combine selected columns of multiple rows into one string

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

Solution

  • You could use array_agg 

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

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