(동영상)여러행값을 하나의 칼럼으로 추출, 가로데이터를 세로로 나열, 계층쿼리, LISTAGG, WM_CONCAT, XMLA…

(동영상)여러행값을 하나의 칼럼으로 추출, 가로데이터를 세로로 나열,  계층쿼리, LISTAGG, WM_CONCAT, XMLAGG 실습, LISTAGG ''문자열 연결의 결과가 너무 깁니다' 오류 해결방법 

 

** 여러행값을 하나의 칼럼으로 추출, 가로데이터를 세로로 나열

** 계층쿼리, LISTAGG, WM_CONCAT, XMLAGG 실습

** LISTAGG ''문자열 연결의 결과가 너무 깁니다' 오류 해결방법

 

-------------------------------------------------------------------------

-- 오라클12C에서 여러행값을 하나의 칼럼으로 추출, 가로데이터를 세로로 나열

-- 사원테이블에서 부서별로 사원들의 이름을 콤마로 구분하여 나열하시오.

-------------------------------------------------------------------------

 

-- 1. 계층쿼리형태로

select deptno

     , ltrim(sys_connect_by_path(ename,','), ',') as enames  -- 콤마(,)로 구분해서 자신까지 경로상의 모든 값을 리턴

from   ( select deptno

              , ename

              , row_number() over (partition by deptno order by ename) as seq

         from   emp )

where connect_by_isleaf = 1    --계층쿼리에서 로우가 자식노드가 있는지 없는지 여부를 체크 있을 경우 0, 없을 경우 1

connect by seq = prior seq + 1  -- 이전행의 seq에 1을 더한것이 자신의 seq인 것(다음행을 찾는 기준)

and deptno = prior deptno        -- 이전행의 부서랑 자신의 부서가 같은 것(다음행을 찾는 기준)

start with seq = 1;  -시작점

 

 

-- 2. 오라클11g R2 이후의 LISTAGG를 이용

SELECT deptno,

          LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS enames

FROM  emp

GROUP BY  deptno;

 

-- 2.1 위 문장을 만약 레코드건수가 1000만건 정도의 myemp1 테이블에서 실행하면 '문자열 연결의 결과가 너무 깁니다' 오류발생

-- 오라클12C 이후 OVERFLOW TRUNCATE를 이용하면 오류 해결가능....

 

-- 아래는 오류

SELECT deptno,

          LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS enames

FROM  myemp1

GROUP BY  deptno;

 

-- 아래는 실행됨

SELECT deptno,

          LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '~~~') WITHIN GROUP (ORDER BY ename) AS enames

FROM  myemp1

GROUP BY  deptno;

 

-- 3. 오라클10g 이후의 WM_CONCAT을 이용, 11g R2이상에서는 실행안됨, 정렬불가, 구분자는 무조건 콤마(,)

SELECT deptno, WM_CONCAT(ename) AS enames

FROM  emp

GROUP BY  deptno;

 

-- 4. 오라클9i 이후, XMLAGG, XMLELEMENT 이용, 정렬가능, 구분자 지정가능

SELECT deptno

         , SUBSTR(

                    -- XMLELEMENT(태그로사용할문자, ',', ename) : ename 칼럼을 <태그로사용할문자> 기준으로 XML 문자열로 반환

                    -- 즉 <태그로사용할문자>,MILLER</태그로사용할문자><태그로사용할문자>,CLARK</태그로사용할문자> 이런형태

                    -- XMLAGG : XML값을 한줄로 만듦, xml 값을 order by 기준에 맞춰서 나란히 붙힌다.

                    -- 즉 <태그로사용할문자>,CLARK</태그로사용할문자><태그로사용할문자>,MILLER</태그로사용할문자> 이런형태

                    -- EXTRACT : XML값중 text 항목만 추려냄

                    -- 즉 ,CLARK,MILLER 이런형태

                    -- SUBSTR로 불필요한 맨앞의 콤마(,) 제거

                    XMLAGG(XMLELEMENT(칼럼 , ',' , ename) ORDER BY ename).EXTRACT('//text()').GETSTRINGVAL()

            , 2) enames

FROM emp

GROUP BY deptno;

Comments