Aggregate rows into one by id - postgres

Question

Example:

user_id activity_type interested_rooms
1       views         1,2
1       application   1
1       search        3,2

I would like to get result:

user_id interested_rooms  
1       "1","2","3"

Then I would like to search all users who for example where interested in 1 room

SELECT user_id
FROM blabla
WHERE interested_rooms IN "1"

How can I get this result by using SQL (postgreSQL)?

Possible duplicate of [Postgresql GROUP\_CONCAT equivalent?](https://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent) — Raymond Nijland, Sep 27 '19 at 08:54
Fix your data model! Numbers should not be stored as strings! Postgres gives you many other options, such as JSON and arrays and junction tables. — Gordon Linoff, Sep 27 '19 at 11:45

Jim Jones · Answer 1 · 2019-09-27T09:36:00.473

There are many ways to achieve what you want. Considering that your table has the following structure ..

CREATE TABLE blabla (user_id INT,activity_type VARCHAR,interested_rooms VARCHAR);
INSERT INTO blabla VALUES (1,'views','1,2'),
                          (1,'application','1'),
                          (1,'search','3,2');

.. you could try a CTE ..

WITH j AS (
  SELECT DISTINCT user_id, unnest(string_to_array(interested_rooms,',')) AS rooms
  FROM blabla ORDER BY rooms
)
SELECT user_id, array_agg(rooms) AS interested_rooms
FROM j GROUP BY user_id

.. that would return the result you want

 user_id | interested_rooms 
---------+------------------
       1 | {1,2,3}

Note that the usage of the functions unnest and string_to_array will depend on the data type of the column interested_rooms which wasn't provided in your question. But it should give you an orientation of what to do. Good luck!

Aggregate rows into one by id - postgres

1 Answers1