[SQL]PIVOT(행을 열로 전환)/UNPIVOT

테이블 생성참고 :

[sql]테이블생성∙수정∙삭제, 제약조건, 데이터 삽입∙수정∙삭제

[sql]제약조건, 데이터 삽입∙수정∙삭제

 

---

모든 학생의 학번, 이름, 과목점수 조회하기

      

        
        
        
        
      
SELECT ST.STID 학번,
ST.STNAME 이름,
SC.SUBJECT 과목,
SC.SCORE 점수
FROM student ST left join scores SC on ST.STID=SC.STID
AND (SC.SUBJECT='국어' or SC.SUBJECT='영어' or SC.SUBJECT='수학');

결과

 

모든 학생의 학번, 이름, 총점 , 평균, 등급, 석차 조회하기

      

        
        
        
        
      
SELECT ST.STID 학번,
ST.STNAME 이름, 
SUM(SC.SCORE)총점,
TO_CHAR(AVG(SC.SCORE),999.99)평균,
CASE
 WHEN TO_CHAR(AVG(SC.SCORE),999.99) BETWEEN 90 AND 100 THEN 'A'
 WHEN TO_CHAR(AVG(SC.SCORE),999.99) BETWEEN 80 AND 89.99 THEN 'B'
 WHEN TO_CHAR(AVG(SC.SCORE),999.99) BETWEEN 70 AND 79.99 THEN 'C'
 WHEN TO_CHAR(AVG(SC.SCORE),999.99) BETWEEN 60 AND 69.99 THEN 'D'
 ELSE 'F'
 END 등급,
RANK()OVER (ORDER BY TO_CHAR(AVG(SC.SCORE),999.99) DESC NULLS LAST) 석차
FROM STUDENT ST,SCORES SC
WHERE ST.STID=SC.STID(+)
GROUP BY ST.STID, ST.STNAME
ORDER BY ST.STID, ST.STNAME
;

결과

위 쿼리들로는 과목별 점수와 총점, 평균, 등급,석차를 함께 확인하려면 쿼리가 복잡해지고 가독성이 떨어지게 된다. 따라서 행을 열로 바꿔주어 학번, 이름, 국어,영어, 수학, 총점, 평균, 등급, 석차를 헤더의 열로 출력하는 쿼리를 작성해보겠다.

1.ORACLE 10G 방식

      

        
        
        
        
      
SELECT SC.STID 학번,
DECODE (SUBJECT,'국어', SCORE) 국어,
DECODE (SUBJECT,'영어', SCORE) 영어,
DECODE (SUBJECT,'수학', SCORE) 수학
FROM SCORES SC;
;

과목별 점수를 확인할 수 있지만 행의 개수가 너무 많고 가독성이 떨어진다.

      

        
        
        
        
      
SUM(DECODE (SUBJECT,'국어', SCORE)) 국어,

따라서 위처럼 SUM함수를 이용해 각 행의 합을 구해서 보기쉽게 나타내준다.

      

        
        
        
        
      
SELECT ST.STID 학번,
ST.STNAME 이름,
SUM(DECODE (SUBJECT,'국어', SCORE)) 국어,
SUM(DECODE (SUBJECT,'영어', SCORE)) 영어,
SUM(DECODE (SUBJECT,'수학', SCORE)) 수학,
SUM(SC.SCORE)총점,
ROUND(AVG(SC.SCORE),3)평균
FROM SCORES SC, STUDENT ST
WHERE SC.STID(+)=ST.STID
GROUP BY ST.STID,ST.STNAME
ORDER BY ST.STID
;

결과

이에 더하여 석차를 구하는 쿼리를 작성해보자.

      

        
        
        
        
      
 CREATE TABLE SCOREGRADE(
  GRADE VARCHAR2(1) PRIMARY KEY,
  LOGRADE NUMBER(3),
  HIGRADE NUMBER(3)
);
INSERT INTO SCOREGRADE VALUES ('A',90,100); 
INSERT INTO SCOREGRADE VALUES ('B',80,89.99); 
INSERT INTO SCOREGRADE VALUES ('C',70,79.99); 
INSERT INTO SCOREGRADE VALUES ('D',60,69.99); 
INSERT INTO SCOREGRADE VALUES ('E',0,59.99);
COMMIT;

비등가 조인을 위한 테이블을 생성해준다.

비등가 조인 참고: [sql]JOIN, ANSI JOIN

[sql]JOIN, ANSI JOIN

 

 

      

        
        
        
        
      
SELECT T.학번, T.이름, T.국어, T.영어, T.수학, T.총점, T.평균,
SG.GRADE 등급,
RANK() OVER (ORDER BY T.총점 DESC NULLS LAST)석차
FROM
 (
SELECT ST.STID 학번,
ST.STNAME 이름,
SUM(DECODE (SUBJECT,'국어', SCORE)) 국어,
SUM(DECODE (SUBJECT,'영어', SCORE)) 영어,
SUM(DECODE (SUBJECT,'수학', SCORE)) 수학,
SUM(SC.SCORE) 총점,
ROUND(AVG(SC.SCORE),3) 평균
FROM SCORES SC RIGHT JOIN STUDENT ST ON SC.STID=ST.STID
GROUP BY ST.STID,ST.STNAME
ORDER BY ST.STID)
T LEFT JOIN SCOREGRADE SG ON T.평균 BETWEEN SG.LOGRADE AND SG.HIGRADE --비등가 조인
ORDER BY T.학번
;

결과

2.ORACLE 11G PIVOT 문법 사용

• 일반적으로 집계함수와 같이 사용한다.
• SQL Server와 같은 일부 데이터베이스에서만 지원된다.
• 피벗할 값이 고정되어 있어야 하므로, 동적 피벗이 필요한 경우는 추가적인 쿼리가 필요하다.

 

SELECT * FROM
(
SELECT 열1, 열2, 집계함수(열3) AS 집계값 FROM 테이블명 GROUP BY 열1, 열) 
PIVOT (집계함수(집계값) FOR 열2 IN (값1, 값2, 값3)
);

      

        
        
        
        
      
SELECT ST.STID 학번,
ST.STNAME 이름,
T.국어 국어,
T.영어 영어,
T.수학 수학,
NVL(T.국어,0)+NVL(T.수학,0)+NVL(T.영어, 0) 총점,
ROUND((NVL(T.국어,0)+NVL(T.수학,0)+NVL(T.영어, 0))/3,3) 평균,
SG.GRADE 학점,
RANK()OVER (
            ORDER BY NVL(T.국어,0)+NVL(T.수학,0)+NVL(T.영어, 0) DESC NULLS LAST) 석차
FROM
(
    SELECT * FROM(
        SELECT STID,SUBJECT,SCORE
        FROM SCORES
        )   
    PIVOT
    (
        SUM(SCORE)
        FOR SUBJECT
        IN ('국어' AS 국어, '영어' AS 영어,'수학' AS 수학)
    )
)T RIGHT JOIN STUDENT ST ON T.STID=ST.STID
         JOIN SCOREGRADE SG
         ON (ROUND((NVL(T.국어,0)+NVL(T.수학,0)+NVL(T.영어, 0))/3,3))
         BETWEEN SG.LOGRADE AND SG.HIGRADE
;

결과

3.UNPIVOT

UNPIVOT은 PIVOT의 반대 개념으로, 열로 구성된 데이터를 행으로 변환하는 데 사용된다.

이때, PIVOT을 통해 생성된 컬럼명 (국어, 영어, 수학)을 UNPIVOT할 때, 컬럼명을 그대로 사용해야 한다.

EX)UNPIVOT ( 점수 FOR 과목 IN (국어, 영어, 수학) )

      

        
        
        
        
      
SELECT 학번, 이름, 과목, 점수
FROM (
    SELECT ST.STID 학번,
           ST.STNAME 이름,
           T.국어, T.영어, T.수학
    FROM (
        SELECT * FROM (
            SELECT STID, SUBJECT, SCORE
            FROM SCORES
        )   
        PIVOT (
            SUM(SCORE)
            FOR SUBJECT
            IN ('국어' AS 국어, '영어' AS 영어, '수학' AS 수학)
        )
    ) T 
    RIGHT JOIN STUDENT ST ON T.STID = ST.STID
)
UNPIVOT (
    점수 FOR 과목 IN (국어, 영어, 수학)
)
ORDER BY 학번, 과목;

결과