Afshin Afshin - 2 months ago 8
SQL Question

Create Squential data from a table in PostGreSQL

I have a huge table like this:

id eventid timestamp
id1 event1 time1
id1 event2 time2
id2 event1 time3
id2 event4 time4
id1 event4 time6


What I want is for each user, its events sorted by time like this:

id event_sequence(nvarchar)
id1 event1,event2,event4
id2 event1,event4


Is there a SQL query I can do this in Postgresql (GreenPlum)?

Answer

Hmmm. I don't know if you can do this in Greenplum. In Postgres, it would look like:

select id, string_agg(event, ',' order by timestamp) as events
from t
group by id;

In Greenplum, I think you need to use:

select id, array_to_string(array_agg(event, ',' order by timestamp)) as events
from t
group by id;
Comments