I would like to concatenate two columns using a group-by query resulting in an array separed with brackets. I know this question is related to this question, but as usual my use-case is a little different.
A simple example (also as SQL Fiddle). Currently my query returns the following:
ID X Y
3 0.5 2.71
3 1.0 2.50
3 1.5 2.33
6 0.5 2.73
6 1.5 2.77
But where I would like concatenate/aggregate the X/Y columns to get the following:
ID XY
3 [[0.5,2.71],[1.0,2.50],[1.5,2.33]]
6 [[0.5,2.73],[1.5,2.77]]
Currently I've tried to concatenate the columns into one as follows:
SELECT "ID",concat_ws(', ',"X", "Y") as XY FROM Table1;
Which returns:
ID xy
3 0.5, 2.71
3 1, 2.50
3 1.5, 2.33
6 0.5, 2.73
And used array_agg():
SELECT "ID",array_to_string(array_agg("X"),',') AS XY
FROM Table1
GROUP BY "ID";
Resulting in:
ID xy
3 0.5,1,1.5
6 0.5
I feel I'm getting closer, but a helping hand would be really appreciated.
Create an array from the two columns, the aggregate the array:
select id, array_agg(array[x,y])
from the_table
group by id;
Note that the default text representation of arrays uses curly braces ( {..}) not square brackets ([..])
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments