[데이터베이스] 81. 통계쿼리 함수들 (5) pivot

select *
from cal;
-- 열에 있는 값을 column으로 옮김.
-- 달력 만들기!

select *
from cal;

select
sum(if(day='일', num_day, 0)) '일',
sum(if(day='월', num_day, 0)) '월',
sum(if(day='화', num_day, 0)) '화',
sum(if(day='수', num_day, 0)) '수',
sum(if(day='목', num_day, 0)) '목',
sum(if(day='금', num_day, 0)) '금',
sum(if(day='토', num_day, 0)) '토'
from cal
where week = 1
union all
select
sum(if(day='일', num_day, 0)) '일',
sum(if(day='월', num_day, 0)) '월',
sum(if(day='화', num_day, 0)) '화',
sum(if(day='수', num_day, 0)) '수',
sum(if(day='목', num_day, 0)) '목',
sum(if(day='금', num_day, 0)) '금',
sum(if(day='토', num_day, 0)) '토'
from cal
where week = 2;

-- 이런식으로 쭉 이어가면 코드가 넘 길어짐.

select
week,
sum(if(day='일', num_day, 0)) '일',
sum(if(day='월', num_day, 0)) '월',
sum(if(day='화', num_day, 0)) '화',
sum(if(day='수', num_day, 0)) '수',
sum(if(day='목', num_day, 0)) '목',
sum(if(day='금', num_day, 0)) '금',
sum(if(day='토', num_day, 0)) '토'
from cal
group by week;

create table cal2
as
select day, num_day
from cal;

select * from cal2