'피벗'에 해당되는 글 1건

피벗 : 행을 열로 변환
언피벗 : 열을 행으로 변환

Oracle 11g 부터 가능

select A.*, case when L1 = '무선단절' and L2 = '신호미약' then '0101' when L1 = '무선단절'
 and L2 = '간섭' then '0102' when L1 = '무선단절'
 and L2 = 'AP 리셋' then '0103' when L1 = '무선단절'
 and L2 = '원인미상' then '0104' when L1 = '무선품질'
 and L2 = '신호미약' then '0201' when L1 = '무선품질'
 and L2 = '재접속' then '0202' when L1 = '연결'
 and L2 = '댁내' then '0301' when L1 = '연결'
 and L2 = 'NW' then '0302' when L1 = '연결'
 and L2 = 'IP망' then '0303' when L1 = '연동'
 and L2 = '단말' then '0401' when L1 = '연동'
 and L2 = '서버' then '0402' when L1 = '연동'
 and L2 = '기타' then '0403' when L1 = '절단'
 and L2 = '호절단' then '0501' when L1 = '음질'
 and L2 = '음질불량(평균)' then '0601' when L1 = '음질'
 and L2 = '음질불량(최소)' then '0602' when L1 = '음질'
 and L2 = 'OneWay Call' then '0603' when L1 = '기타'
 and L2 = '원인미상' then '0701' end as middlecode
 from (
select substr(PTYPE, 0, instr (PTYPE, ' ', 1, 1) - 1) as L1, substr(PTYPE, instr (PTYPE, ' ', 1, 1) + 1,
 length(PTYPE) - instr (PTYPE, ' ', 1, 1)) L2, CALL_TRY_FAIL, CUST_CNT, VOC_CNT,
 TOTAL_CUST_CNT, round(CALL_TRY_FAIL / TOTAL_CUST_CNT * 100, 8) as RATE
 from (
select PTYPE, sum(val) as CALL_TRY_FAIL, count(distinct (case when PTYPE = '무선단절 신호미약'
 and VAL > 0 then CUST_NUM when PTYPE = '무선단절 간섭'
 and VAL > 0 then CUST_NUM when PTYPE = '무선단절 AP 리셋'
 and VAL > 0 then CUST_NUM when PTYPE = '무선단절 원인미상'
 and VAL > 0 then CUST_NUM when PTYPE = '무선품질 신호미약'
 and VAL > 0 then CUST_NUM when PTYPE = '무선품질 재접속'
 and VAL > 0 then CUST_NUM when PTYPE = '연결 댁내'
 and VAL > 0 then CUST_NUM when PTYPE = '연결 NW'
 and VAL > 0 then CUST_NUM when PTYPE = '연결 IP망'
 and VAL > 0 then CUST_NUM when PTYPE = '연동 단말'
 and VAL > 0 then CUST_NUM when PTYPE = '연동 서버'
 and VAL > 0 then CUST_NUM when PTYPE = '연동 기타'
 and VAL > 0 then CUST_NUM when PTYPE = '절단 호절단'
 and VAL > 0 then CUST_NUM when PTYPE = '음질 음질불량(평균)'
 and VAL > 0 then CUST_NUM when PTYPE = '음질 음질불량(최소)'
 and VAL > 0 then CUST_NUM when PTYPE = '음질 OneWay Call'
 and VAL > 0 then CUST_NUM when PTYPE = '기타 원인미상'
 and VAL > 0 then CUST_NUM else null end)) as CUST_CNT, sum((case when PTYPE = '무선단절 신호미약'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선단절 간섭'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선단절 AP 리셋'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선단절 원인미상'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선품질 신호미약'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '무선품질 재접속'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연결 댁내'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연결 NW'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연결 서버'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연동 단말'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연동 서버'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '연동 기타'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '절단 호절단'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '음질 음질불량(평균)'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '음질 음질불량(최소)'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '음질 OneWay Call'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC when PTYPE = '기타 원인미상'
 and VAL > 0 and QTY_VOC > 0 then QTY_VOC else 0 end)) as VOC_CNT
 from (
select DATE_FLD, DATE_FLD1, CUST_NUM, MODEL_TYPE, MODEL_DEV_NM, MODEL_MAC,
 MODEL_FW_TYPE, TEL_NO, FAIL_TYPE, QTY_VOC, CALL_TRY_FAIL_CNT, PTYPE, VAL
 from (
select *
 from TB_SCLLUxxx
 where 1 = 1 and DATE_FLD1 >= 1411484400
 and DATE_FLD1 < 1411570800 and MODEL_TYPE in (21, 30, 31)) UNPIVOT (VAL FOR
 PTYPE in (WD_SIGNAL as '무선단절 신호미약', WD_INTERFER as '무선단절 간섭', WD_APRESET as '무선단절 AP 리셋',
 WD_ETC as '무선단절 원인미상', WQ_SIGNAL as '무선품질 신호미약', WQ_RECONN as '무선품질 재접속',
 CF_HOMENET as '연결 댁내', CF_NETWORK as '연결 NW', CF_IPNET as '연결 IP망', IF_MODEL as '연동 단말',
 IF_SERVER as '연동 서버', IF_ETC as '연동 기타', CALL_DISCONN as '절단 호절단', BAD_CALL_AVG
 as '음질 음질불량(평균)', BAD_CALL_MIN as '음질 음질불량(최소)', BAD_CALL_OWC as '음질 OneWay Call',
 TOTAL_ETC as '기타 원인미상')))
 group by PTYPE), (
select sum(CUST_CNT) as TOTAL_CUST_CNT
 from (
select distinct model_dev_nm, model_fw_type
 from TB_SCLLUxxx
 where 1 = 1 and DATE_FLD1 >= 1411484400
 and DATE_FLD1 < 1411570800 and MODEL_TYPE in (21, 30, 31)) MDL left join (
select model_Dev_nm, data_Dev_nm as model_fw_type, sum(cust_cnt) as CUST_CNT
 from tb_susrsxxx
 where 1 = 1 and DATE_FLD1 = 1411484400
 and data_dev = 5 and svc_type = 2 and MODEL_TYPE in (21, 30, 31)
 group by model_dev_nm, data_dev_nm) USR on MDL.MODEL_DEV_NM = USR.MODEL_DEV_NM
 and MDL.MODEL_FW_TYPE = USR.MODEL_FW_TYPE)) A
 order by middlecode