Concatenate field from multiple rows into single field

Question

Barriers: I can't CREATE anything. No GTT's, PROCs, etc. I'm querying an Exadata data lake and whatever script I create will be executed from Toad or SQL Developer.

I have a SELECT query that returns 3 rows:

• Date
• IDKey
• EVENT

for each Date, IDKey, there are likely a dozen or so EVENT's. Example:

 DATE       IDKEY EVENT    
10/17/2016  300328  EVENT1    
10/17/2016  300328  EVENT3    
10/17/2016  300328  EVENT4    
10/17/2016  300440  EVENT1    
10/17/2016  300440  EVENT2    
10/17/2016  300440  EVENT5 

On a given DATE, an IDKey can have multiple EVENTS, each generating a new row.

Goal: to create a single row that groups DATE and IDKEY, concatenating all unique EVENTS into a single field;

DATE  IDKEY  EVENT    
10/17/2016 300328 'EVENT1 | EVENT3 | EVENT4'    
10/17/2016 300440 'EVENT1 | EVENT2 | EVENT5'

Answer 1

Looks like a group by with listagg:

with demo (dt, id, event) as
   ( select date '2016-10-17', 300328, 'EVENT1' from dual union all
     select date '2016-10-17', 300328, 'EVENT3' from dual union all
     select date '2016-10-17', 300328, 'EVENT4' from dual union all
     select date '2016-10-17', 300440, 'EVENT1' from dual union all
     select date '2016-10-17', 300440, 'EVENT2' from dual union all
     select date '2016-10-17', 300440, 'EVENT5' from dual 
   )
select dt, id
     , listagg(event, '|') within group (order by event) as events
from   demo
group by dt, id;

DT                  ID EVENTS
----------- ---------- --------------------------------
17/10/2016      300328 EVENT1|EVENT3|EVENT4
17/10/2016      300440 EVENT1|EVENT2|EVENT5

There could he problems if there are a huge number of eventnos.