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?
Since 9.0 the string_agg aggregate function can be used within an aggregate expression:
SELECT id,
string_agg(some_column, ',')
FROM the_table
GROUP BY id;
If the column isn't a text type, it will need to be cast:
SELECT id,
string_agg(some_column::text, ',')
FROM the_table
GROUP BY id;
array_agg) e.g. string_agg(some_column, ',' ORDER BY some_column) or even string_agg(surname || ', ' || forename, '; ' ORDER BY surname, forename)
distinct works with string_agg, so one can use string_agg(distinct some_solumn, ',')
TEXT if it is a non-stringable value (ie. uuid). This would look like string_agg(some_column::text, ',')
value is string. For example, if the value is integer value need to be casted to string like value::character varyingIn Postgres 8.4 or later, this is probably a good starting point:
SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field
The array_agg function 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 the method yourself prior to use:
CREATE AGGREGATE array_agg (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
(paraphrased from the PostgreSQL documentation)
Clarifications:
array_to_string() or string_agg() (the group_concat analogous function added in 9.0) quote strings with embedded commas, resulting in an incorrect number of elements in the resulting list.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).
SELECT array_to_string(array(SELECT a FROM b),', ');
Will do as well.
Try like this:
select field1, array_to_string(array_agg(field2), ',')
from table1
group by field1;
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"
KI
and the version to work on the array type:
select
array_to_string(
array(select distinct unnest(zip_codes) from table),
', '
);
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.
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, ',')
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
ORDER BY in an inner query? Won't the ordering get lost anyway?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
