SQL pivot with duplicate IDs to return earliest time-CodePudding

I have a table that looks like

ID	Event	Time
1	a	1:00 PM
1	a	1:05 PM
1	b	2:00 PM
1	c	3:00 PM
1	d	4:00 PM
1	d	4:02 PM
2	a	11:30
2	b	3:04 PM
2	b	3:06 PM
2	c	2:15 PM
2	c	5:20 PM
3	a	12:30 PM
3	b	4:00 PM
3	c	1:00 PM
4	c	10:00 PM

I have a code that pivots like

select * from

(

select

id

,event

,time

,row_number() over(partition by event order by time) as line

from table

)

pivot(min(time) for event in (a,b,c)) where line=1

;

I'm getting a table that returns only the IDs with earliest times per column per ID like below, with null values. It won't even return the 4th ID since it doesn't have the earliest time for event c. This is a large dataset with thousands of IDs. It looks like it is only returning the ID with the earliest time for the # of events. So if I have 5 event times, it will only return me with 5 IDs max with earliest time for one of the column. I'm hoping it makes sense.

ID	a	b	c
1	(null)	2:00 PM	(null)
2	11:30	(null)	(null)
3	(null)	(null)	1:00 PM

What I want is a table like above that returns the earliest time for each event per ID. Like below:

ID	a	b	c
1	1:00	2:00	3:00
2	11:30	3:04	2:15

The table looks like it needs to be pivoted so this is what I've tried so far.

CodePudding user response：

How about conditional aggregation?

Setting date (time) format (you don't have to do that):

SQL> alter session set nls_date_format = 'hh24:Mi';

Session altered.

Sample data:

SQL> with test (id, event, time) as
  2    (select 1, 'a', to_date('13:00', 'hh24:mi') from dual union all
  3     select 1, 'a', to_date('13:05', 'hh24:mi') from dual union all
  4     select 1, 'b', to_date('14:00', 'hh24:mi') from dual union all
  5     select 1, 'c', to_date('15:00', 'hh24:mi') from dual union all
  6     select 1, 'd', to_date('16:00', 'hh24:mi') from dual union all
  7     select 1, 'd', to_date('16:02', 'hh24:mi') from dual union all
  8     --
  9     select 2, 'a', to_date('11:30', 'hh24:mi') from dual union all
 10     select 2, 'b', to_date('15:04', 'hh24:mi') from dual union all
 11     select 2, 'b', to_date('15:06', 'hh24:mi') from dual union all
 12     select 2, 'c', to_date('14:15', 'hh24:mi') from dual union all
 13     select 2, 'c', to_date('17:20', 'hh24:mi') from dual union all
 14     --
 15     select 3, 'a', to_date('12:30', 'hh24:mi') from dual union all
 16     select 3, 'b', to_date('16:00', 'hh24:mi') from dual
 17    )

Query begins here:

 18  select id,
 19    min(case when event = 'a' then time end) as a,
 20    min(case when event = 'b' then time end) as b,
 21    min(case when event = 'c' then time end) as c
 22  from test
 23  group by id
 24  order by id;

        ID A     B     C
---------- ----- ----- -----
         1 13:00 14:00 15:00
         2 11:30 15:04 14:15
         3 12:30 16:00

SQL>

CodePudding user response：

Use PIVOT without the ROW_NUMBER filter:

SELECT *
FROM   table_name
PIVOT (
  MIN(time)
  FOR event IN ('a' AS a, 'b' AS b, 'c' AS c)
);

Which, for the sample data:

CREATE TABLE table_name (ID, Event, Time) AS
  SELECT 1, 'a', INTERVAL '13:00:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 1, 'a', INTERVAL '13:05:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 1, 'b', INTERVAL '14:00:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 1, 'c', INTERVAL '15:00:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 1, 'd', INTERVAL '16:00:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 1, 'd', INTERVAL '16:02:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 2, 'a', INTERVAL '11:30:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 2, 'b', INTERVAL '15:04:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 2, 'b', INTERVAL '15:06:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 2, 'c', INTERVAL '14:15:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 2, 'c', INTERVAL '17:20:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 3, 'a', INTERVAL '12:30:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 3, 'b', INTERVAL '16:00:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 3, 'c', INTERVAL '13:00:00' HOUR TO SECOND FROM DUAL UNION ALL
  SELECT 3, 'c', INTERVAL '22:00:00' HOUR TO SECOND FROM DUAL;

Outputs:

ID	A	B	C
1	00 13:00:00.000000	00 14:00:00.000000	00 15:00:00.000000
2	00 11:30:00.000000	00 15:04:00.000000	00 14:15:00.000000
3	00 12:30:00.000000	00 16:00:00.000000	00 13:00:00.000000

fiddle