더 좋은 방법이 있을겁니다...

WITH t AS
(
    SELECT 'ITEM1' ITEM, 'A100' PROCESS, 'START' TYPE, '20190517054347812375' TIME, 'P1' SITE, 25 QTY1, 53900 QTY2 FROM DUAL
    UNION ALL
    SELECT 'ITEM1', 'A840', 'END', '20190520152934435414', 'P1', 25, 53838 FROM DUAL
    UNION ALL
    SELECT 'ITEM1', 'B100', 'START', '20190522145923690148', 'P2', 25, 53838 FROM DUAL
    UNION ALL
    SELECT 'ITEM1', 'B700', 'END', '20190603003540682511', 'P2', 25, 51785 FROM DUAL
    UNION ALL
    SELECT 'ITEM1', 'D100', 'START', '20190819120529218469', 'P4', 12, 24973 FROM DUAL
    UNION ALL
    SELECT 'ITEM1', 'D450', 'END', '20190820150630346346', 'P4', 12, 24973 FROM DUAL
)
SELECT
    A.ITEM
    , (A.TIME - B.TIME) TIME
    , A.SITE
    , A.QTY1
    , A.QTY2
FROM
    (
    SELECT
        ITEM
        , TIME
        , SITE
        , QTY1
        , QTY2
    FROM T
    WHERE TYPE = 'END'
    ) A JOIN (
    SELECT
        ITEM
        , TIME
        , SITE
        , QTY1
        , QTY2
    FROM T
    WHERE TYPE = 'START'
    ) B
    ON A.SITE = B.SITE
;

with t as
(select 'ITEM1' item, 'A100' process, 'START' type, '20190517054347812375' time, 'P1' site, 25 qty1, 53900 qty2 from dual union all
 select 'ITEM1', 'A840', 'END', '20190520152934435414', 'P1', 25, 53838 from dual union all
 select 'ITEM1', 'B100', 'START', '20190522145923690148', 'P2', 25, 53838 from dual union all
 select 'ITEM1', 'B700', 'END', '20190603003540682511', 'P2', 25, 51785 from dual union all
 select 'ITEM1', 'D100', 'START', '20190819120529218469', 'P4', 12, 24973 from dual union all
 select 'ITEM1', 'D450', 'END', '20190820150630346346', 'P4', 12, 24973 from dual
)
select item
     , edt
     , sdt
     , to_timestamp(edt, 'yyyymmddhh24missff6') 
       - to_timestamp(sdt, 'yyyymmddhh24missff6') as diff_dt
     , site
     , qty1
     , qty2
from (select item
           , min(decode(type, 'END', time)) as edt
           , min(decode(type, 'START', time)) as sdt
           , site
           , qty1
           , min(decode(type, 'END', qty2)) as qty2
      from t
      group by item, substr(process, 1, 1), site, qty1
     )
order by site
;

WITH T AS (
    SELECT 'item1' ITEM,    'A100' PROCESS,      'START' TYPE,   '20190517054347812375' TIME,  'P1' SITE,     25 QTY1,     53900 QTY2 FROM DUAL UNION ALL 
    SELECT 'item1' ITEM,    'A840' PROCESS,      'END' TYPE,   '20190520152934435414' TIME,  'P1' SITE,     25 QTY1,     53838 QTY2 FROM DUAL UNION ALL   
    SELECT 'item1' ITEM,    'B100' PROCESS,      'START' TYPE,   '20190522145923690148' TIME,  'P2' SITE,     25 QTY1,     53838 QTY2 FROM DUAL UNION ALL 
    SELECT 'item1' ITEM,    'B700' PROCESS,      'END' TYPE,   '20190603003540682511' TIME,  'P2' SITE,     25 QTY1,     51785 QTY2 FROM DUAL UNION ALL   
    SELECT 'item1' ITEM,    'D100' PROCESS,      'START' TYPE,   '20190819120529218469' TIME,  'P4' SITE,     12 QTY1,     24973 QTY2 FROM DUAL UNION ALL 
    SELECT 'item1' ITEM,    'D450' PROCESS,      'END' TYPE,   '20190820150630346346' TIME,  'P4' SITE,     12 QTY1,     24973 QTY2 FROM DUAL
)
SELECT 
    ITEM,SITE,STIME,ETIME,ETIME_QTY1,ETIME_QTY2
FROM (
    SELECT ITEM,TYPE,TIME,SITE,QTY1,QTY2 FROM T
)
PIVOT(
    MIN(QTY1) QTY1,MIN(QTY2) QTY2,MIN(TIME)
    FOR TYPE
    IN ('START' STIME,'END' ETIME)
)