ForeverConfused ForeverConfused - 1 year ago 74
SQL Question

How do I aggregate text values into a single record

For example a site traffic log

User Site

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 1,2 3,

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

Answer Source

You can use array_agg method defined here. Like

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

    site     | array_agg 
-------------+-----------  | {3} | {1,2}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download