이건 제가 만든 쿼리입니다.
select 업체명,노선번호,버스유형,기점,종점,면허대수,거리km,운행횟수,배차간격,첫차출발시간,막차출발시간
from(select
TB_ADM_BUSCOMP.COMPNM as 업체명,
TB_ADM_BUSROUTE.ROUTENO as 노선번호,
decode(TB_ADM_BUSROUTE.ROUTETPCD, 1, '지선', 2, '간선' , 3, '좌석' , 4, '광역' , 5, '리무진', 6, '마을버스', 7, '순환', 8 ,'급행간선', 9 , '지선순환' ) as 버스유형,
FC_BUSSTOP_BSTOPNM(TB_ADM_BUSROUTE.ORIGIN_BSTOPID) AS 기점,
FC_BUSSTOP_BSTOPNM(TB_ADM_BUSROUTE.DEST_BSTOPID) AS 종점,
TB_ADM_BUSROUTE.LIC_BUSCNT as 면허대수,
TB_ADM_BUSROUTE.ROUTELEN * (0.001) as 거리km,
TB_ADM_BUSROUTE.RUNCNT as 운행횟수,
TB_ADM_ROUTESCHED.MIN_ALLOCGAP||'~'||TB_ADM_ROUTESCHED.MAX_ALLOCGAP as 배차간격,
decode(TB_ADM_ROUTESCHED.DOWTPCD, 10, '상행', 11, '하행' ) as 상하행,
TB_ADM_ROUTESCHED.FBUS_DEPHMS as 첫차출발시간,
TB_ADM_ROUTESCHED.LBUS_DEPHMS as 막차출발시간
from
TB_ADM_BUSCOMP,
TB_ADM_ROUTETOCOMP,
TB_ADM_BUSROUTE,
TB_ADM_ROUTESCHED,
TB_ADM_BUSSTOP
where TB_ADM_BUSCOMP.COMPID = TB_ADM_ROUTETOCOMP.COMPID
and TB_ADM_ROUTETOCOMP.routeid = TB_ADM_BUSROUTE.routeid
and TB_ADM_BUSROUTE.routeid = TB_ADM_ROUTESCHED.routeid
and TB_ADM_BUSROUTE.USEYN = 1
and TB_ADM_BUSCOMP.USEYN = 1
and TB_ADM_BUSROUTE.routeid = 165000002
order by 3)
group by 업체명,노선번호,버스유형,기점,종점,면허대수,거리km,운행횟수,배차간격,첫차출발시간,막차출발시간
결과를 첫차출발시간 컬럼에 - 상행 0500, 하행 0445
막차출발시간 컬럼에 - 상행 2300, 하행 2300
이렇게 한줄로 만들고 싶은데 구글에 검색해서 이것저것해보아도 계속 오류가 나네요 ㅠ
select '상행'||rtrim(substr(sumf_tm,0,instr(sumf_tm,',')),',')||', 하행'||ltrim(substr(sumf_tm,instr(sumf_tm,',')),',') from ( select LISTAGG(first_tm,',') WITHIN GROUP(ORDER BY bus_no) as sumf_tm ,LISTAGG(last_tm,',') WITHIN GROUP(ORDER BY bus_no) as suml_tm from ( select '2'as bus_no,'0500' as first_tm , '2300' as last_tm from dual union all select '2'as bus_no,'0445' as first_tm , '2300' as last_tm from dual ) )비슷하게 흉내는 내봤씁니다.
SELECT 업체명, 노선번호, 버스유형, 기점, 종점, 면허대수, 거리km, 운행횟수, 배차간격 , LISTAGG(상하행||' '||첫차출발시간, ', ') WITHIN GROUP(ORDER BY 상하행) 첫차출발시간 , LISTAGG(상하행||' '||막차출발시간, ', ') WITHIN GROUP(ORDER BY 상하행) 막차출발시간 FROM (SELECT a.compnm AS 업체명 , c.routeno AS 노선번호 , DECODE(c.routetpcd, 1, '지선' , 2, '간선' , 3, '좌석' , 4, '광역' , 5, '리무진' , 6, '마을버스' , 7, '순환' , 8, '급행간선' , 9, '지선순환' ) AS 버스유형 , fc_busstop_bstopnm(c.origin_bstopid) AS 기점 , fc_busstop_bstopnm(c.dest_bstopid ) AS 종점 , c.lic_buscnt AS 면허대수 , c.routelen * (0.001) AS 거리km , c.runcnt AS 운행횟수 , d.min_allocgap||'~'||d.max_allocgap AS 배차간격 , DECODE(d.dowtpcd, 10, '상행', 11, '하행') AS 상하행 , d.fbus_dephms AS 첫차출발시간 , d.lbus_dephms AS 막차출발시간 FROM tb_adm_buscomp a , tb_adm_routetocomp b , tb_adm_busroute c , tb_adm_routesched d -- 간단한 알리아스 사용으로 쿼리를 보기 좋게 -- , tb_adm_busstop -- 불필요한 조인 제거 WHERE a.compid = b.compid AND b.routeid = c.routeid AND c.routeid = d.routeid AND c.USEYN = 1 AND a.USEYN = 1 AND c.routeid = 165000002 -- ORDER BY 3 -- 불필요한 정렬 제거 ) GROUP BY 업체명, 노선번호, 버스유형, 기점, 종점, 면허대수, 거리km, 운행횟수, 배차간격 ;
오라클 버전이 어떻게 되나요?
LISTAGG 는 오라클 11G 부터 가능합니다.
http://gurubee.net/article/55512