mysql // 피벗 테이블

 

예제 테이블

create table sakila.cal(
    week varchar(100),
    day varchar(3),
    num_day varchar(100) 
) engine=InnoDB default charset=utf8;

 

예제 데이터

Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','일','1');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','월','2');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','화','3');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','수','4');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','목','5');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','금','6');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','토','7');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','일','8');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','월','9');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','화','10');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','수','11');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','목','12');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','금','13');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','토','14');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','일','15');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','월','16');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','화','17');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','수','18');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','목','19');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','금','20');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','토','21');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','일','22');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','월','23');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','화','24');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','수','25');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','목','26');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','금','27');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','토','28');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('5','일','29');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('5','월','30');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('5','화','31');

 

주, 요일, 날짜가 있는 테이블

SELECT * FROM sakila.cal;

 

이해를 돕기위해 순차적으로 진행할 것이다.

 

1. 첫째 주

SELECT * FROM sakila.cal WHERE week LIKE '1' ;

첫째 주의 데이터가 모두 출력되었다.

 

 

2. 첫째 주의 일요일만 출력

SELECT if(DAY ='일', NUM_DAY,"") "일" FROM sakila.cal WHERE week LIKE '1' ;

조건 문을 걸어서 첫째주의 일요일은 출력하고 일요일이 아니면 공백으로 두게 하였다.

 

3. 첫째 주를 MAX로 주어서 일요일만 출력

SELECT MAX(if(DAY ='일', NUM_DAY,"")) "일" FROM sakila.cal WHERE week LIKE '1';

GROUP BY가 없어도 열이 하나이기 때문에 문제없이 출력된다.

공백("")과 "1"에서 MAX값은 "1"이다. (반대로 MIN을 주면 공백이 출력될 것이다.)

 

4. 모든 주를 그룹으로 묶고 일요일만 출력

SELECT MAX(if(DAY ='일', NUM_DAY,"")) "일" FROM sakila.cal GROUP BY week;

week를 그룹으로 묶고 일요일만 출력해보자.

 

5. 모든 주를 그룹으로 묶고 모든 요일 출력

SELECT
  MAX(if(DAY ='일', NUM_DAY,"")) "일", 
  MAX(if(DAY ='월', NUM_DAY,"")) "월",
  MAX(if(DAY ='화', NUM_DAY,"")) "화", 
  MAX(if(DAY ='수', NUM_DAY,"")) "수",
  MAX(if(DAY ='목', NUM_DAY,"")) "목", 
  MAX(if(DAY ='금', NUM_DAY,"")) "금",
  MAX(if(DAY ='토', NUM_DAY,"")) "토"
FROM CAL
GROUP BY WEEK
ORDER BY WEEK;

달력이 완성되었다.

 

 

 

번외

1. 그룹으로 묶지 않고, Max를 주지 않았을 경우

SELECT
  if(DAY ='일', NUM_DAY,"") "일", 
  if(DAY ='월', NUM_DAY,"") "월",
  if(DAY ='화', NUM_DAY,"") "화", 
  if(DAY ='수', NUM_DAY,"") "수",
  if(DAY ='목', NUM_DAY,"") "목", 
  if(DAY ='금', NUM_DAY,"") "금",
  if(DAY ='토', NUM_DAY,"") "토"
FROM CAL
ORDER BY WEEK;

공백인 행까지 모두 나온다.