데이터베이스 질문과 답변
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | 아래의 쿼리를 조회하면 STND_DATA, CT_10~23, TIME, S 이렇게 조회가 됩니다.질문사항은 CT_10~23을 TIME, S 와 같이 세로로 표시하고 싶은데아무리 해봐도 필드 추가형태로 되지 않아 자문을 구합니다.WITH TEMP_FAD_100 AS (SELECT SUBSTR(STND_DATE,5,4) STND_DATE , SUM(NVL(TIME_10, 0)) AS "10" , SUM(NVL(TIME_11, 0)) AS "11" , SUM(NVL(TIME_12, 0)) AS "12" , SUM(NVL(TIME_13, 0)) AS "13" , SUM(NVL(TIME_14, 0)) AS "14" , SUM(NVL(TIME_15, 0)) AS "15" , SUM(NVL(TIME_16, 0)) AS "16" , SUM(NVL(TIME_17, 0)) AS "17" , SUM(NVL(TIME_18, 0)) AS "18" , SUM(NVL(TIME_19, 0)) AS "19" , SUM(NVL(TIME_20, 0)) AS "20" , SUM(NVL(TIME_21, 0)) AS "21" , SUM(NVL(TIME_22, 0)) AS "22" , SUM(NVL(TIME_23, 0)) AS "23" , MAX(CT_10) AS CT_10 , MAX(CT_11) AS CT_11 , MAX(CT_12) AS CT_12 , MAX(CT_13) AS CT_13 , MAX(CT_14) AS CT_14 , MAX(CT_15) AS CT_15 , MAX(CT_16) AS CT_16 , MAX(CT_17) AS CT_17 , MAX(CT_18) AS CT_18 , MAX(CT_19) AS CT_19 , MAX(CT_20) AS CT_20 , MAX(CT_21) AS CT_21 , MAX(CT_22) AS CT_22 , MAX(CT_23) AS CT_23 FROM T_FAD_100 A , ( SELECT DY_SALE , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '10' THEN 1 ELSE 0 END ) CT_10 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '11' THEN 1 ELSE 0 END ) CT_11 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '12' THEN 1 ELSE 0 END ) CT_12 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '13' THEN 1 ELSE 0 END ) CT_13 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '14' THEN 1 ELSE 0 END ) CT_14 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '15' THEN 1 ELSE 0 END ) CT_15 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '16' THEN 1 ELSE 0 END ) CT_16 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '17' THEN 1 ELSE 0 END ) CT_17 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '18' THEN 1 ELSE 0 END ) CT_18 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '19' THEN 1 ELSE 0 END ) CT_19 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '20' THEN 1 ELSE 0 END ) CT_20 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '21' THEN 1 ELSE 0 END ) CT_21 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '22' THEN 1 ELSE 0 END ) CT_22 , SUM(CASE WHEN SUBSTR(DT_SALE,9,2) = '23' THEN 1 ELSE 0 END ) CT_23 FROM T_RSM_100, T_COM_410 WHERE DY_SALE BETWEEN TO_CHAR(TO_DATE('20220426')-6,'YYYYMMDD') AND '20220426' AND DY_SALE = DY_DATE GROUP BY DY_SALE ORDER BY DY_SALE ) B WHERE A.STND_DATE = B.DY_SALE(+) AND STND_DATE BETWEEN '20220420' AND '20220426' --:sToDt and :dt GROUP BY STND_DATE ORDER BY STND_DATE desc ) SELECT * FROM TEMP_FAD_100 UNPIVOT ( s FOR time IN ("10", "11" , "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23") ) |
by 마농
[2022.05.26 18:44:29]
최종 결과표 그려주세요.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(
) 버튼을 클릭하여 작성 하시면 됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.