Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have a table and I'd like to pull one row per id with field values concatenated.

In my table, for example, I have this:

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200
And I'd like to output:
TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200
In MySQL I was able to use the aggregate function GROUP_CONCAT, but that doesn't seem to work here... Is there an equivalent for PostgreSQL, or another way to accomplish this?
                I think the best answer is still in another question: stackoverflow.com/a/47638417/243233
– Jus12
                Dec 5, 2017 at 14:27
                Note that the syntax also allows you to specify the order of values in the string (or array, using array_agg) e.g. string_agg(some_column, ',' ORDER BY some_column) or even string_agg(surname || ', ' || forename, '; ' ORDER BY surname, forename)
– IMSoP
                May 16, 2013 at 14:33
                It's awesome that distinct works with string_agg, so one can use string_agg(distinct some_solumn, ',')
– arun
                Jul 4, 2018 at 0:33
                Note that you may need to cast the column value to TEXT if it is a non-stringable value (ie. uuid). This would look like string_agg(some_column::text, ',')
– Kendall
                Sep 11, 2019 at 16:56
                If column type is integer, don't forget to convert or use concat(column, '') for implicit conversion
– Takman
                Oct 1, 2020 at 14:48
                PS, string_agg(value, ',') can be use only if the value is string. For example, if the value is integer value need to be casted to string like value::character varying
– ZenithS
                Nov 25, 2020 at 7:19
This is probably a good starting point (version 8.4+ only):
SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field
array_agg returns an array, but you can CAST that to text and edit as needed (see clarifications, below).
Prior to version 8.4, you have to define it yourself prior to use:
CREATE AGGREGATE array_agg (anyelement)
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
(paraphrased from the PostgreSQL documentation)
Clarifications:
The result of casting an array to text is that the resulting string starts and ends with curly braces.  Those braces need to be removed by some method, if they are not desired.  
Casting ANYARRAY to TEXT best simulates CSV output as elements that contain embedded commas are double-quoted in the output in standard CSV style. Neither array_to_string() or string_agg() (the "group_concat" function added in 9.1) quote strings with embedded commas, resulting in an incorrect number of elements in the resulting list.
The new 9.1 string_agg() function does NOT cast the inner results to TEXT first.  So "string_agg(value_field)" would generate an error if value_field is an integer.  "string_agg(value_field::text)" would be required.  The array_agg() method requires only one cast after the aggregation (rather than a cast per value).
                To get CSV the query should be:  SELECT id_field, array_to_string(array_agg(value_field1), ','), array_to_string(array_agg(value_field2),', ') FROM data_table GROUP BY id_field
– Nux
                Feb 16, 2012 at 16:08
                You can't use array_to_string in all cases here.  If your value_field contains an embedded comma, the resulting CSV is incorrect.  Using array_agg() and casting to TEXT properly quotes strings with embedded commas.  The only caveat is that it also includes the starting and ending curly braces, hence my statement "and edit as needed".  I will edit to clarify that point.
– Matthew Wood
                Feb 21, 2012 at 20:00
                Is it possible to do something like in this comment, where you aggregate in a certain order? How would you handle grouping by one column and ordering by another one (for example, to concatenate variables within a longitudinal data set)?
– Michael A
                Jan 8, 2015 at 18:22
Assuming that the table your_table has three columns (name, id, value), the query is this one:
  select name, 
         array_to_string(array_agg(id), ','), 
         array_to_string(array_agg(value), ',')
    from your_table
group by name
order by name
"TM67"  "4,9,72"    "32556,98200,22300"
"TM99"  "2,3"       "23009,11200"
                @EmilVikström: you have right to be wrong, but read carefully. It's not only different, but I gave an example, which works with array type - like zip_codes being character varying(5)[]. Also, I've verified that for my purpose - unnest is needed, otherwise you will see ERROR:  cannot accumulate arrays of different dimensionality.
– Sławomir Lenart
                Feb 6, 2019 at 13:47
In my experience, I had bigint as column type. So The below code worked for me. I am using PostgreSQL 12.
Type cast is happening here. (::text).
string_agg(some_column::text, ',')
Select First_column,LISTAGG(second_column,',') 
    WITHIN GROUP (ORDER BY second_column) as Sec_column, 
    LISTAGG(third_column,',') 
    WITHIN GROUP (ORDER BY second_column) as thrd_column 
FROM tablename 
GROUP BY first_column
                I tested it on rextester.com/l/postgresql_online_compiler and didn't work: 42883: function listagg(text, unknown, text) does not exist
– Manuel Romeiro
                Feb 4, 2020 at 12:07
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.