How to aggregate Postgres table so that ID is unique and column values are collected in array?

Question

I'm not sure how to call what I'm trying to do, so trying to look it up didn't work very well. I would like to aggregate my table based on one column and have all the rows from another column collapsed into an array by unique ID.

| ID | some_other_value |
-------------------------
| 1  | A                |
| 1  | B                |
| 2  | C                |
| .. | ...              |

To return

| ID | values_array     |
-------------------------
| 1  | {A, B}           |
| 2  | {C}              |

Sorry for the bad explanation, I'm really lacking the vocabulary here. Any help with writing a query that achieves what's in the example would be very much appreciated.

Gowdhaman008 · Accepted Answer · 2018-08-27 16:31:10Z

1

Try the following.

select id, array_agg(some_other_value order by some_other_value ) as values_array from <yourTableName> group by id

You can also check here.

answered Aug 27, 2018 at 16:31

Gowdhaman008

1,3331 gold badge10 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam · Accepted Answer · 2018-08-27 16:27:26Z

1

See Aggregate Functions documentation.

SELECT
    id,
    array_agg(some_other_value)
FROM
    the_table
GROUP BY
    id;

answered Aug 27, 2018 at 16:27

Adam

5,6296 gold badges35 silver badges41 bronze badges

Collectives™ on Stack Overflow

How to aggregate Postgres table so that ID is unique and column values are collected in array?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related