예를들어
SELECT 'AAAAA' AS A , 'BBBBB' AS B , '10' AS C FROM DUAL
UNION ALL
SELECT 'EEEEE' AS A , 'BBBBB' AS B , '10' AS C FROM DUAL
UNION ALL
SELECT 'FFFFF' AS A , 'CCCCC' AS B , '10' AS C FROM DUAL
이렇게 데이타 가 있다면 컬럼 KEY 값이 B컬럼인데 C의 컬럼값이 같은경우 조회시
AAAAA BBBBB 10
AAAAA BBBBB
FFFFF CCCCC 10
이런식으로 병합된거 처럼 나오게끔하는 방법이 있을까요??
WITH T AS (SELECT 'AAAAA' AS A, 'BBBBB' AS B, '10' AS C FROM DUAL UNION ALL SELECT 'EEEEE' AS A, 'BBBBB' AS B, '10' AS C FROM DUAL UNION ALL SELECT 'FFFFF' AS A, 'CCCCC' AS B, '10' AS C FROM DUAL) SELECT A , B , C , MIN(A) KEEP (DENSE_RANK FIRST ORDER BY A ASC) OVER (PARTITION BY B) A2 , B B2 , CASE WHEN ROW_NUMBER() OVER(PARTITION BY B ORDER BY A ASC) = 1 THEN C END C2 FROM T;
WITH T AS ( SELECT 'AAAAA' AS A , 'BBBBB' AS B , '10' AS C FROM DUAL UNION ALL SELECT 'EEEEE' AS A , 'BBBBB' AS B , '10' AS C FROM DUAL UNION ALL SELECT 'FFFFF' AS A , 'CCCCC' AS B , '10' AS C FROM DUAL ) SELECT A , B , CASE WHEN ROW_NUMBER() OVER(PARTITION BY B , C ORDER BY A ) = 1 THEN C END C FROM T