[Database] 20. PIVOT

1. 달력을 출력해보자

select *
from cal;

2. 샘플링 코드 1 (1주만 출력)

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;

3. 샘플링 코드2 (2주 출력)

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;

4. 샘플링 코드를 참고해서, GROUP BY로 묶어준다.

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;

5. 결과를 가지고 실제 테이블로 만들기

create table cal2
as
select day, num_day
from cal;

select * from cal2;

6. cal2를 가지고 주 열을 만들어라. (case when then 사용)

select day,num_day,
case 
	when num_day <= 7 then "1"
    when num_day <= 14 then "2"
    when num_day <= 21 then "3"
    when num_day <= 28 then "4"
    else '5'
end '주'
from cal2;

7. cal2를 가지고 주 열을 만들어라. (CEIL함수 사용)

SELECT day, num_day, CEIL(num_day / 7) AS '주'
FROM cal2;