WITH TMP AS ( SELECT 'A' AS CD, '과일' AS CDNM FROM DUAL UNION ALL SELECT 'B' AS CD, '야채' AS CDNM FROM DUAL UNION ALL SELECT 'C' AS CD, '고기' AS CDNM FROM DUAL ), TMP2 AS ( SELECT 'A' AS CD, 'AA' CD2, '사과' AS CDNM FROM DUAL UNION ALL SELECT 'B' AS CD, 'BB' CD2, '당근' AS CDNM FROM DUAL ) SELECT TMP2.CD2, TMP2.CDNM, TMP.CD, TMP.CDNM FROM TMP, TMP2 WHERE TMP.CD = TMP2.CD(+)
다음과 같은 자료에서
CD2 | CDNM | CD | CDNM | 종류 |
AA | 사과 | A | 과일 | Y |
AA | 사과 | B | 야채 | |
AA | 사과 | C | 고기 | |
BB | 당근 | A | 과일 | |
BB | 당근 | B | 야채 | Y |
BB | 당근 | C | 고기 |
이런 포멧의 결과를 얻고 싶은데요...도움 부탁드립니다..요즘들어 문의가 많네요..ㅠㅠ
우리집아찌 님 답글 감사드립니다..
만약 종류에 'Y' 대신에 Count를 한다면 쿼리도 달라져야 하겠죠?
WITH TMP AS ( SELECT 'A' AS CD, '과일' AS CDNM FROM DUAL UNION ALL SELECT 'B' AS CD, '야채' AS CDNM FROM DUAL UNION ALL SELECT 'C' AS CD, '고기' AS CDNM FROM DUAL ), TMP2 AS ( SELECT 'A' AS CD, 'AA' CD2, '사과' AS CDNM FROM DUAL UNION ALL SELECT 'A' AS CD, 'AB' CD2, '사과' AS CDNM FROM DUAL UNION ALL SELECT 'B' AS CD, 'BB' CD2, '당근' AS CDNM FROM DUAL ) SELECT TMP2.CD2, TMP2.CDNM, TMP.CD, TMP.CDNM FROM TMP, TMP2 WHERE TMP.CD = TMP2.CD(+)
CDNM | CD | CDNM | COUNT |
사과 | A | 과일 | 2 |
사과 | B | 야채 | |
사과 | C | 고기 | |
당근 | A | 과일 | |
당근 | B | 야채 | 1 |
당근 | C | 고기 |
WITH TMP AS ( SELECT 'A' AS CD, '과일' AS CDNM FROM DUAL UNION ALL SELECT 'B' AS CD, '야채' AS CDNM FROM DUAL UNION ALL SELECT 'C' AS CD, '고기' AS CDNM FROM DUAL ), TMP2 AS ( SELECT 'A' AS CD, 'AA' CD2, '사과' AS CDNM FROM DUAL UNION ALL SELECT 'B' AS CD, 'BB' CD2, '당근' AS CDNM FROM DUAL ) SELECT MAX(TMP2.CD2) OVER(PARTITION BY TMP2.CD||TMP2.CD) AS CD2 , MAX(TMP2.CDNM) OVER(PARTITION BY TMP2.CD||TMP2.CD) AS CDNM , TMP.CD , TMP.CDNM , DECODE(TMP.CD, TMP2.CD, 'Y') AS TYP FROM TMP LEFT OUTER JOIN TMP2 PARTITION BY (TMP2.CD) ON TMP.CD = TMP2.CD ; SELECT CDNM1 , CD1 , CDNM2 , COUNT(DECODE(TYP, 'Y', 1)) AS CNT FROM ( SELECT MAX(TMP2.CD2) OVER(PARTITION BY TMP2.CD||TMP2.CD) AS CD2 , MAX(TMP2.CDNM) OVER(PARTITION BY TMP2.CD||TMP2.CD) AS CDNM1 , TMP.CD AS CD1 , TMP.CDNM AS CDNM2 , DECODE(TMP.CD, TMP2.CD, 'Y') AS TYP FROM TMP LEFT OUTER JOIN TMP2 PARTITION BY (TMP2.CD) ON TMP.CD = TMP2.CD ) GROUP BY CDNM1, CD1, CDNM2, CD2 ORDER BY CD2, CD1 ;