Postgresql GROUP CONCAT equivalent

Question

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

User · Answer

My sugestion in postgresql  SELECT cpf           nome           telefone   FROM         SELECT cpf              nome              STRING AGG CONCAT WS        DDD 1  TELEFONE 1       AS telefone        FROM               SELECT DISTINCT                FROM temp bd              ORDER BY cpf DESC   AS y       GROUP BY 1 2   AS x

User · Answer

SELECT array to string array SELECT a FROM b           Will do as well

User · Answer

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

User · Answer

Hope below Oracle query will work   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

User · Answer

and the version to work on the array type   select   array to string      array select distinct unnest zip codes  from table

User · Answer

Since 9 0 this is even easier   SELECT id          string agg some column       FROM the table GROUP BY id

User · Answer

Try like this   select field1  array to string array agg field2        from table1 group by field1

[sql] Postgresql GROUP_CONCAT equivalent?

Examples related to sql

Examples related to postgresql

Examples related to group-concat

Examples related to string-aggregation