데이터베이스 질문과 답변
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
10 8750
20 FORD 3000
20 ADAMS 1100
20 JONES 2975
20 SCOTT 3000
20 SMITH 800
20 10875
30 WARD 1250
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30 9400
이 쿼리를
DEPT_10 ENAME_10 SAL_10 DEPT_20 ENAME_20 SAL_20 DEPT_30 ENAME_30 SALE_30
------- -------- ------ ------- -------- ------ ------- -------- -----
10 CLARK 2450 20 ADAMS 1100 30 ALLEN 1600
10 KING 5000 20 FORD 3000 30 BLAKE 2850
10 MILLER 1300 20 JONES 2975 30 JAMES 950
10 8750 20 SCOTT 3000 30 MARTIN 1250
20 SMITH 800 30 TURNER 1500
20 10875 30 WARD 1250
30 9400
이렇게 부서번호 별로 가로로 뽑고 싶습니다.
아직 초보라 감도 잘 안오네요ㅜㅜ....
도와주세요ㅠㅠ
-- 1. 부서별 소계 --
SELECT deptno
, ename
, SUM(sal) sal
FROM emp
GROUP BY deptno, ROLLUP(ename)
;
-- 2. 피벗 --
SELECT *
FROM (SELECT deptno
, ename
, SUM(sal) sal
, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) rn
FROM emp
GROUP BY deptno, ROLLUP(ename)
)
PIVOT (MIN(deptno) deptno, MIN(ename) ename, MIN(sal) sal FOR deptno IN (10, 20, 30))
ORDER BY rn
;
-- 3. 불필요하다고 생각되는 부분(dept_10, dept_20, dept_30) 제거 : 타이틀과 값이 중복되어 표현되고 있음
SELECT *
FROM (SELECT deptno
, ename
, SUM(sal) sal
, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) rn
FROM emp
GROUP BY deptno, ROLLUP(ename)
)
PIVOT (-- MIN(deptno) deptno,
MIN(ename) ename, MIN(sal) sal FOR deptno IN (10, 20, 30))
ORDER BY rn
;
감사합니다 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.