Postgresql: Group rows in a row and add array

Question

Hi i have a table like this;

+----+----------+-------------+
| id | room_id  |    house_id |
+----+----------+-------------+
|  1 |        1 |           1 |
|  2 |        2 |           1 |
|  3 |        3 |           1 |
|  4 |        1 |           2 |
|  5 |        2 |           2 |
|  6 |        3 |           2 |
|  7 |        1 |           3 |
|  8 |        2 |           3 |
|  9 |        3 |           3 |
+----+-------+----------------+

and i want to create a view like this

+----+----------+-------------+
| id | house_id |       rooms |
+----+----------+-------------+
|  1 |        1 |     [1,2,3] |
|  2 |        2 |     [1,2,3] |
|  3 |        3 |     [1,2,3] |
+----+-------+----------------+

i tried many ways but i cant gruop them in one line

Thanks for any help.

Gordon Linoff · Accepted Answer · 2021-05-25 18:00:00Z

2

You can use array_agg():

select house_id, array_agg(room_id order by room_id) as rooms
from t
group by house_id;

If you want the first column to be incremental, you can use row_number():

select row_number() over (order by house_id) as id, . . .

answered May 25, 2021 at 18:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Postgresql: Group rows in a row and add array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related