ForeverConfused ForeverConfused - 5 months ago 14
SQL Question

How do I aggregate text values into a single record

For example a site traffic log

User Site
1 Example.com
2 Example.com
3 Google.com


I want to ask the database "Give me a view of all the users that visited each site" and output something like this

Site Ids
Example.com 1,2
Google.com 3,


I don't mind if it requires some python or pl/pgsql. Any ideas that would lead me in the right direction.

Answer

You can use array_agg method defined here. Like

with a (user_id, site) as (values(1, 'Example.com'), (2, 'Example.com'), (3, 'Google.com'))
select site, array_agg(user_id) 
from a
group by 1;

    site     | array_agg 
-------------+-----------
 Google.com  | {3}
 Example.com | {1,2}
Comments