Join of data from one column into one string

Question

in postgreSQL database I have table with Name(string) column.

ID| Name
1 | a
2 | b
3 | c
4 | d

With one query I would like to retrieve all names from the table into one string in the format:

'a', 'b', 'c', 'd'

Gordon Linoff · Answer 1 · 2021-07-29T12:06:01.303

1

You can use string_agg():

select string_agg(distinct '''' || name || '''', ', ' order by id)
from t;

For most purposes, though, I would suggest using arrays instead:

select array_agg(name order by id)
from t

edited Jul 29 '21 at 12:06

answered Jul 29 '21 at 11:20

Gordon Linoff

Thank you, I forgot that the data has to be unique. – panmigal Jul 29 '21 at 11:22
`select string_agg('''' || subquery.name || '''', ', ' order by id) from (select distinct name from table) as subquery` WORKS – panmigal Jul 29 '21 at 11:25
1

@panmigal: I'd rather use the simple `select string_agg(distinct ...)`. – Thorsten Kettner Jul 29 '21 at 11:34

1 Answers1