피벗

데이터 회전을 뜻하는 피벗은 테이블의 데이터를 행에서 열로 변환하는 프로세스를 말한다. 일반적으로 보고서에서 적합한 정보를 제공하는 요약 테이블이다.
MySQL에는 피벗 테이블을 생성하는 내장 함수가 없기 때문에 쿼리문으로 직접 작성해야 하는데, 여기에서는 2가지 방법으로 피벗 테이블을 생성했다.

샘플 테이블

name, subjection, marks가 있는 student 테이블을 만들어 사용한다.

-- 테이블 생성
CREATE TABLE student(name varchar(20),subjectid int(10), marks int(10));
-- 데이터 삽입
INSERT INTO student VALUES ('Sam',1,70);
INSERT INTO student VALUES ('Sam',2,77);
INSERT INTO student VALUES ('Sam',3,71);
INSERT INTO student VALUES ('Sam',4,70);
INSERT INTO student VALUES ('Sam',1,99);
INSERT INTO student VALUES ('John',1,89);
INSERT INTO student VALUES ('John',2,87);
INSERT INTO student VALUES ('John',3,88);
INSERT INTO student VALUES ('John',4,89); 
INSERT INTO student VALUES ('Martin',1,60);
INSERT INTO student VALUES ('Martin',2,47);
INSERT INTO student VALUES ('Martin',3,68);
INSERT INTO student VALUES ('Martin',4,39);

1. MAX() 함수와 IF 문 사용

학생의 과목별 점수를 컴파일한 결과다.
학생 한 명 당 하나의 행만 필요하므로 이름으로 GROUP BY 해야 한다.
각 열에 해대 하나의 조건을 지정한다.

• 똑같은 과목에서 2개 이상의 점수가 있을 경우 가장 높은 점수를 가져온다.

SELECT name,
	MAX(IF(subjectid=1, marks, NULL)) sub1,
    MAX(IF(subjectid=2, marks, NULL)) sub2,
    MAX(IF(subjectid=3, marks, NULL)) sub3,
    MAX(IF(subjectid=4, marks, NULL)) sub4
FROM student
GROUP BY name;

2. CASE 문 사용

CASE 문을 활용해 동일한 피벗 테이블을 만들 수 있다.
위와 같이 MAX() 함수를 사용했으며, 일부 점수가 존재하지 않으면 NULL 값을 취한다.

SELECT name,
	MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) sub1,
	MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) sub2,
	MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) sub3,
	MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) sub4
FROM student
GROUP BY name;

---

참고 : MySQL의 피벗 테이블
이미지 출처 : Pivot — Rows to Columns