컬럼의 값들을 한줄 (가로) 로 출력하는 방법이다.
oracle의 쿼리의 값을 간혹 가로(옆으로)로 표시해야 할 경우가 생긴다
test 테이블이 있다.
seq | title |
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
SELECT title
FROM test
를 실행하면
title |
| aaa |
| bbb |
| ccc |
로 표현된다.
oracle 10g 부터 지원하는 WM_CONCAT함수가 있다.
비공식 함수이다.
구분자가 무조건 콤마(,)이며 그렇기 때문에 데이터 중 콤마(,) 가 존재하지 않을 때 사용해야 한다.
또한 정렬이 안된다.
SELECT WM_CONCAT(title)
FROM test
를 실행하면
title |
| aaa,bbb,ccc |
로 표시할 수 있다.
2개이상 사용시 order by를 하지 않으면 순서가 섞일 수 있다
조금 더 안정적인 함수는
listagg 가 있다
oracle 11g 부터 사용가능하다.
select listagg(title, ',') within group (order by title) from test
오라클 행을 열로 바꾸는 PIVOT 사용법 및 설명 및 PIVOT 대체하기
오라클에서 PIVOT을 사용하면 세로로 출력되는 형식의 데이터를 가로로 뒤집어 출력할 수 있습니다.
부서별 직책별 평균 급여 구하기
오라클 사용자라면 누구나 알법한 SCOTT 형님(?)의 EMP 테이블입니다.
사원정보를 담고 있으며, JOB(직책)과 SAL(급여), 부서번호(DEPTNO)가 실습시 사용될 컬럼입니다.
각각의 부서별 직책별 평균 급여를 구해보도록 하겠습니다.
SELECT DEPTNO, JOB, AVG(SAL) FROM EMP GROUP BY DEPTNO, JOB ORDER BY DEPTNO, JOB; | cs |
간단하게 출력되었으나 여러 행(ROW)로 출력되어 뭔가 한번에 정리가 되지 않아 불편합니다.
세로를 가로로 바꾼다면
세로(ROW)로 출력된 직책들을 아래와 같이 가로(COLUMN)로 직책별로 나열되면 편할 것 같습니다.
첫번째 방법
먼저 PIVOT이 없다는 가정하에 두가지 정도 방법이 있을 것 같습니다. 첫번째로는 서브쿼리에서 DECODE함수나 CASE문을 통해 부서별 직책별 합계를 구한 후에 다시 부서별로 GROUP BY하여 AVG로 평균값을 구하는 것입니다.
SELECT DEPTNO, AVG(ANALYST) AS ANALYST, AVG(CLERK) AS CLERK, AVG(MANAGER) AS MANAGER, AVG(PRESIDENT) AS PRESIDENT, AVG(SALESMAN) AS SALESMAN FROM ( SELECT DEPTNO, DECODE(JOB, 'ANALYST', SUM(SAL)) AS ANALYST, DECODE(JOB, 'CLERK', SUM(SAL)) AS CLERK, DECODE(JOB, 'MANAGER', SUM(SAL)) AS MANAGER, DECODE(JOB, 'PRESIDENT', SUM(SAL)) AS PRESIDENT, DECODE(JOB, 'SALESMAN', SUM(SAL)) AS SALESMAN FROM EMP GROUP BY DEPTNO, JOB ) GROUP BY DEPTNO ORDER BY DEPTNO; | cs |
두번째 방법
훨씬 심플한 방법인데 DEPTNO로만 GROUP BY한 상태에서 DECODE를 통해 직책별 급여 컬럼으로 분류한 뷰를 만든 상태에서 곧바로 AVG로 직책별 급여를 집계하여 출력하는 방법입니다. 두가지 결과는 모두 같습니다.
SELECT DEPTNO, AVG(DECODE(JOB, 'ANALYST', SAL)) AS ANALYST, AVG(DECODE(JOB, 'CLERK', SAL)) AS CLERK, AVG(DECODE(JOB, 'MANAGER', SAL)) AS MANAGER, AVG(DECODE(JOB, 'PRESIDENT', SAL)) AS PRESIDENT, AVG(DECODE(JOB, 'SALESMAN', SAL)) AS SALESMAN FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO; | cs |
PIVOT을 사용한 방법
PIVOT을 사용하면 아래와 같이 바꿀 수 있습니다.
SELECT * FROM ( SELECT DEPTNO, JOB, SAL FROM EMP ) PIVOT ( AVG(SAL) FOR JOB IN ('ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN') ) ORDER BY DEPTNO; | cs |
결과는 같습니다.
PIVOT 사용 형식
사용 형식을 살펴보자면 아래와 같습니다.
SELECT 출력할 컬럼 FROM [PIVOT 대상 테이블] PIVOT ( [집계함수] FOR [가로로 출력할 열] IN ([조건 대상 값]) ) ORDER BY DEPTNO; | cs |
PIVOT 설명 및 동작원리
PIVOT은 함수가 아니라 문법이기 때문에 FROM절 다음에 오도록 사용합니다. PIVOT은 내부적으로 GROUP BY를 행, 열 두가지 방향으로 실행한다는 사실을 알아야 합니다.
모든 사원의 직책별 평균 급여를 PIVOT을 통해 가로로 나열하여 보도록 하겠습니다.
SELECT * FROM ( SELECT JOB, SAL FROM EMP ) PIVOT ( AVG(SAL) FOR JOB IN ('ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN') ); | cs |
결과는 아래와 같습니다.
여기서 달라진 점은 FROM절에 있는 대상 테이블입니다. 여기서는 서브쿼리를 사용했는데 JOB과 SAL컬럼을 명시했습니다. PIVOT문을 사용할때 FROM절에 대상이 되는 테이블의 모든 컬럼은 GROUP BY의 대상이 됩니다.
SELECT JOB, SAL FROM EMP; | cs |
앞서 위에서 살펴본 PIVOT 사용 형식을 참고하시고 이 중 PIVOT문에 가로로 출력할 열은 가로로 나열할때의 GROUP BY의 대상이 됩니다. 즉 JOB이 GROUP BY 되어 집계된 후 가로로 나열됩니다. 집계함수에 사용되는 대상 열은 집계 대상이므로 제외하고 나머지 열들은 세로(ROW)로 나열할때 GROUP BY 대상이 됩니다.
설명이 어려운데, 아래 PIVOT문을 보도록 합니다. PIVOT 대상 테이블에는 ENAME, JOB, SAL컬럼이 있습니다.
SELECT * FROM ( SELECT ENAME, JOB, SAL FROM EMP ) PIVOT ( AVG(SAL) FOR JOB IN ('ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN') ); | cs |
이중 PIVOT 대상 컬럼인 JOB은 가로로 GROUP BY 됩니다. SAL은 집계함수의 대상이 됩니다. 나머지 컬럼인 ENAME컬럼이 우리가 평소에 사용하는 세로로 GROUP BY의 대상이 됩니다.
즉 정리해보자면 아래와 같습니다.
ENAME으로 GROUP BY를 최초로 실행한 상태에서(세로)
JOB으로 GROUP BY를 실행(가로)
SAL로 AVG(평균) 집계
따라서 최초 FROM절에 명시된 대상 테이블 또는 뷰의 컬럼들에 따라 PIVOT의 결과 또한 달라지게 되는 것입니다.
PIVOT 별칭 주기
PIVOT에 별칭을 사용하고자 하는 경우 FOR IN문에 AS를 사용하면 됩니다.
SELECT 매니저, 영업사원 FROM ( SELECT JOB, SAL FROM EMP ) PIVOT ( AVG(SAL) FOR JOB IN ('MANAGER' AS 매니저, 'SALESMAN' AS 영업사원) ); | cs |
연관글
Tag column, oracle, pivot, 대신, 대체, 돌리기, 뒤집기, 설명, 열, 오라클, 원리, 피벗, 행