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:
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'
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments