[데이터 베이스] 10-5. pivot

Pivot 기본 (달력)

(1)

-- pivot
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;

(2)

-- group by 사용
-- 값 자체를 컬럼으로 사용 - pivot
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;

Cal2 Table 만들기

-- cal2 테이블 만들기
create table cal2
as
select day, num_day
from cal;

select * from cal2;