[Oracle] 콤마를 Row로 변환
[Oracle] 콤마를 Row로 변환 WITH TMP AS ( SELECT '사과,배,감자,복숭아,수박,포도' AS Col1 FROM DUAL ) SELECT REGEXP_SUBSTR (TMP.Col1, '[^,]+', 1, LEVEL) PIVOT_COL FROM DUAL, TMP CONNECT BY REGEXP_SUBSTR (TMP.Col1,'[^,]+', 1, LEVEL) IS NOT NULL; PIVOT_COL -----------------사과배감자복숭아수박포도
더보기
[Oracle] 각 그룹별 SUM 및 특정 컬럼 첫번째 Row 가져오기
[Oracle] 각 그룹별 SUM 및 특정 컬럼 첫번째 Row 가져오기 WITH TEMP AS ( SELECT 'A' AS "품목1", 'A1-1' AS "품목2", 100 AS "품목 당 목표매출량", 20 AS "매출량" FROM DUAL UNION ALL SELECT 'A' AS "품목1", 'A1-1' AS "품목2", 100 AS "품목 당 목표매출량", 40 AS "매출량" FROM DUAL UNION ALL SELECT 'A' AS "품목1", 'A1-2' AS "품목2", 200 AS "품목 당 목표매출량", 20 AS "매출량" FROM DUAL UNION ALL SELECT 'A' AS "품목1", 'A1-2' AS "품목2", 200 AS "품목 당 목표매출량", 40 AS "매출량"..
더보기
[Oracle] 시:분:초 <-> hour, minute, second 등으로 변환
[Oracle] 시:분:초 hour, minute, second 등으로 변환 ------------------------------------------------------------------------------ hh24:mi:s를 숫자(시,분,초)로 변환---------------------------------------------------------------------------- 1. 문자열 (hh24:mi:s) 을 시, 분, 초 로 변환 - 딱 안떨어질 수 있음 SELECT '00:23:45' "시간", TO_NUMBER(TO_CHAR(TO_TIMESTAMP('00:23:45', 'hh24:mi:ss'),'sssss'))/3600 "시로 변환", TO_NUMBER(TO_CHAR(TO_T..
더보기
[Oracle] 그룹별 최종일자 ROW 가져오기
[Oracle] 그룹별 최종일자 ROW 가져오기 WITH TEMP AS ( SELECT 'A' GRP, TO_DATE('2018-11-01','YYYY-MM-DD') DT, 50 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 100 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-03','YYYY-MM-DD') DT, 70 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-03','YYYY-MM-DD') DT, 150 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2..
더보기
[Oracle] 계층형 원 조상 가져오기(CONNECT_BY_ROOT)
[Oracle] 계층형 원 조상 가져오기(CONNECT_BY_ROOT) WITH TEMP AS ( SELECT '0' Seq, NULL P_Level, 'A-1' C_Level FROM DUAL UNION ALL SELECT '1' Seq,'A-1' P_Level, 'A-2' C_Level FROM DUAL UNION ALL SELECT '2' Seq,'A-1' P_Level, 'A-3' C_Level FROM DUAL UNION ALL SELECT '3' Seq,'A-2' P_Level, 'A-4' C_Level FROM DUAL UNION ALL SELECT '4' Seq,'A-4' P_Level, 'A-5' C_Level FROM DUAL UNION ALL SELECT '5' Seq,'A-5' P_..
더보기