데이타 조회 컬럼 값 병합???이런기능이 있나요 0 2 612

by lgxj20 [SQL Query] [2018.11.30 17:35:30]


예를들어 

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
                           

이런식으로 병합된거 처럼 나오게끔하는 방법이 있을까요??

by 신이만든지기 [2018.11.30 18:37:30]
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;

 


by 우리집아찌 [2018.12.03 09:32:14]
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 

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입