행열 바꾸는 오라클 로직

WITH TEST AS (
        SELECT 'A' AS GBN, '001' AS VAL FROM DUAL UNION ALL
        SELECT 'A' AS GBN, '002' AS VAL FROM DUAL UNION ALL
        SELECT 'A' AS GBN, '003' AS VAL FROM DUAL UNION ALL
        SELECT 'A' AS GBN, '004' AS VAL FROM DUAL UNION ALL
        SELECT 'B' AS GBN, '001' AS VAL FROM DUAL UNION ALL
        SELECT 'B' AS GBN, '002' AS VAL FROM DUAL UNION ALL
        SELECT 'B' AS GBN, '003' AS VAL FROM DUAL
)
SELECT  GBN
      , SUBSTR(XMLAGG(XMLELEMENT(A,',' || VAL) ORDER BY VAL).EXTRACT('//text()'), 2) AS VAL
  FROM  TEST
 GROUP
    BY  GBN

 

 

XMLAGG 함수와 XMLELEMENT 함수가 생소하기만 하다. 더군다나 길다.

 

머 더 좋은건 없을까.?

 

물론 있다. ㅋ~

 

 

WITH TEST AS (
        SELECT 'A' AS GBN, '001' AS VAL FROM DUAL UNION ALL
        SELECT 'A' AS GBN, '002' AS VAL FROM DUAL UNION ALL
        SELECT 'A' AS GBN, '003' AS VAL FROM DUAL UNION ALL
        SELECT 'A' AS GBN, '004' AS VAL FROM DUAL UNION ALL
        SELECT 'B' AS GBN, '001' AS VAL FROM DUAL UNION ALL
        SELECT 'B' AS GBN, '002' AS VAL FROM DUAL UNION ALL
        SELECT 'B' AS GBN, '003' AS VAL FROM DUAL
)
SELECT  GBN
      , WM_CONCAT(VAL) AS VAL
  FROM  TEST
 GROUP
    BY  GBN

 

 

Oracle SYS_CONNECT_BY_PATH (여러 ROW를 한 컬럼으로)

[출처] Oracle SYS_CONNECT_BY_PATH (여러 ROW를 한 컬럼으로)|작성자 늦지않았다면

 

 

기준이 되는 컬럼의 여러 로우가 동일할 경우,

다른 컬럼의 ROW들을 한 컬럼으로 연결해서 나열해주는 함수

오라클 9i 이상부터 지원 

  

 

1. 먼저 화면에 보여줄 내용들을 조회

 

sELECT B.CD, B.CD_NM, B.REF_CD1
  FROM T_COMM_CD B
  WHERE B.CD_CLS_CD = 'C054'   ; 

 

 

 

2. 기준이 될 컬럼을 정하고 ROW NUMBER를 매겨준다

   (REF_CD1 컬럼을 기준으로 같은값끼리 순차적으로 번호 생성됨)

 

SELECT B.CD, B.CD_NM, B.REF_CD1
, ROW_NUMBER() OVER (PARTITION BY B.REF_CD1 ORDER BY B.REF_CD1, CD) RNUM
FROM T_COMM_CD B
WHERE B.CD_CLS_CD = 'C054' ;
 

 

3. 생성된 ROW NUMBER를 이용하여 CONNECT BY(계층형 함수) 해주고

   REF_CD1 컬럼의 로우끼리 비교하여 같은 값이면 CD 컬럼의 ROW값을 계속 연결

SELECT REF_CD1, SYS_CONNECT_BY_PATH(CD, ',') PATH
FROM
(
SELECT B.CD, B.CD_NM, B.REF_CD1
, ROW_NUMBER() OVER (PARTITION BY B.REF_CD1 ORDER BY B.REF_CD1, CD) RNUM
FROM T_COMM_CD B
WHERE B.CD_CLS_CD = 'C054'
)
WHERE REF_CD1 IS NOT NULL -- REF_CD1값이 있는것만 필요했기때문에 IS NOT NULL 조건을 줌
START WITH RNUM=1
CONNECT BY PRIOR RNUM=RNUM-1 AND PRIOR REF_CD1=REF_CD1
GROUP BY REF_CD1 ; 

 

 

4. 연결된 값 중 가장 큰 값을 구해주면 최종 연결된 값만 보여주게된다.

SELECT REF_CD1, MAX(SYS_CONNECT_BY_PATH(CD, ',')) PATH
FROM
(
SELECT B.CD, B.CD_NM, B.REF_CD1
, ROW_NUMBER() OVER (PARTITION BY B.REF_CD1 ORDER BY B.REF_CD1, CD) RNUM
FROM T_COMM_CD B
WHERE B.CD_CLS_CD = 'C054'
)
WHERE REF_CD1 IS NOT NULL
START WITH RNUM=1
CONNECT BY PRIOR RNUM=RNUM-1 AND PRIOR REF_CD1=REF_CD1
GROUP BY REF_CD1 ;

 

 

 

ORACLE 리스트되는 로우의 특정 컬럼값을 구분자를 통해 하나로 합치는 쿼리

 

 

아래의 쿼리를 보시면 1번과 2번의 특징이 여러개의 로우의 특정컬럼을 특정 구분자로 하나로 합치는 쿼리 입니다.

과거에 한번 보내 드린적이 있는데..

이번 EBOOK 의 쿼리를 분석한 결과 3번과 같이 XML 함수를 써서 쿼리를 짠게 보입니다.

그래서 개발자 분들께서도 한번도 상기 시키고자 메일 보냅니다.

 

1번의 단점은 해당 로우가 일정하게 정해져 있어야 하지만 2, 3번로우는 관계없이 다 처리 되므로 장점을 반드시 숙지 하시기 바랍니다.

9I는 아직 XML 펑션이 좀 무거운 편이니 너무 많이 쓰지 않는게 좋습니다.

 

--1. 리스트되는 로우의 특정 컬럼값을 구분자를 통해 하나로 합치는 쿼리 입니다.

SELECT MIN(DECODE(RN, 1, NAME))||','||MIN(DECODE(RN, 2, NAME))||','||MIN(DECODE(RN, 3, NAME))||','||MIN(DECODE(RN, 4, NAME))FROM (

    SELECT 1 RN, 'A' NAME FROM DUAL

    UNION ALL

    SELECT 2 RN, 'B' NAME FROM DUAL

    UNION ALL

    SELECT 3 RN, 'C' NAME FROM DUAL

    UNION ALL

    SELECT 4 RN, 'D' NAME FROM DUAL

);

 

--2. BOM 모델을 이용한 방법

SELECT MAX(SUBSTR(SYS_CONNECT_BY_PATH(NAME, ','), 2)) FROM (

    SELECT 1 RN, 'A' NAME FROM DUAL

    UNION ALL

    SELECT 2 RN, 'B' NAME FROM DUAL

    UNION ALL

    SELECT 3 RN, 'C' NAME FROM DUAL

    UNION ALL

    SELECT 4 RN, 'D' NAME FROM DUAL

)

START WITH RN = 1

CONNECT BY PRIOR RN = RN-1;

 

--3. 9I 부터는 XML 함수를 이용해서 간단하게 표현됩니다.

--여기서  EXTRACT('//text()')에서 text()는 반드시 소문자이여야 합니다.

SELECT

SUBSTR(XMLAGG(XMLELEMENT(NAME, ','||NAME) ORDER BY RN).EXTRACT('//text()').GETSTRINGVAL(), 2) AS NAME

FROM (

    SELECT 1 RN, 'A' NAME FROM DUAL

    UNION ALL

    SELECT 2 RN, 'B' NAME FROM DUAL

    UNION ALL

    SELECT 3 RN, 'C' NAME FROM DUAL

    UNION ALL

    SELECT 4 RN, 'D' NAME FROM DUAL

)

;

[출처] [ORACLE] 로우를 컬럼으로 변환 (행을 열로) |작성자 Real Iron