SELECT ROUTEID as 노선ID,
FC_BUSROUTE_ROUTENO(ROUTEID) 노선번호,
FC_BUS_CARREGNO(BUSID) AS 버스번호,
FC_BUSSTOP_SHORTID(NODEID) as 정류장ID,
FC_BUSSTOP_BSTOPNM(NODEID) AS 정류소명,
DECODE(FC_PATHSEQ_DIRCD(ROUTEID, PATHSEQ), 0, '상행', 1, '하행') AS 상행하행,
DECODE(EVTSUBTPCD, 2, '도착', 3, '출발') AS 이벤트,
EVT_OCCURDT AS 이벤트시간
FROM
(SELECT
ROUTEID,
BUSID,
NODEID,
EVTSUBTPCD,
EVT_OCCURDT,
PATHSEQ
FROM TB_BMH_RUNEVTCOLL
where TB_BMH_RUNEVTCOLL.EVTTPCD = 21
AND EVTSUBTPCD IN (2, 3)
and TB_BMH_RUNEVTCOLL.NODEID in (121000006,
121000005,
121000003,
121000002,
121000001)
AND EVT_OCCURDT> TO_DATE('20190329000000','YYYYMMDDHH24MISS')
AND EVT_OCCURDT < TO_DATE('20190329235959','YYYYMMDDHH24MISS')
ORDER BY EVT_OCCURDT) A
order by 3,8,5
쿼리 결과는 아래랑 같은데 이 결과 아래것들 처럼 한줄로 만들수 있을까요?
둘중하나의 형태만 되면 되는데 구글링을 해봐도 방법을 모르겠습니다
SELECT routeid AS 노선id , fc_busroute_routeno(routeid) AS 노선번호 , fc_bus_carregno(busid) AS 버스번호 , fc_busstop_shortid(nodeid) AS 정류장id , fc_busstop_bstopnm(nodeid) AS 정류소명 , DECODE(fc_pathseq_dircd(routeid, pathseq), 0, '상행', 1, '하행') AS 상행하행 , MIN(DECODE(evtsubtpcd, 2, evt_occurdt)) 도착 , MIN(DECODE(evtsubtpcd, 3, evt_occurdt)) 출발 FROM tb_bmh_runevtcoll WHERE evttpcd = 21 AND evtsubtpcd IN (2, 3) AND nodeid IN (121000006, 121000005, 121000003, 121000002, 121000001) AND evt_occurdt >= TO_DATE('20190329', 'yyyymmdd') AND evt_occurdt < TO_DATE('20190329', 'yyyymmdd') + 1 GROUP BY routeid , busid , nodeid , pathseq ORDER BY 버스번호, 도착, 정류소명 ;