PIVOT - 행을 열로 바꾸기

통계페이지와 같은 집계가 필요한 경우 종종 로우를 칼럼으로 바꿔서 출력해야될 경우가 있다.

로우를 칼럼으로 바꾸는 방법을 3개 정도 알아보고자 한다.

1.DECODE 혹은 CASE 문을 사용하는 방법

2.WITH 절을 이용한 방법

3.PIVOT 함수를 이용하는 방법

 

이 글에서 사용하는 테스트 데이터는 다음과 같다.

create table table_score(
	years varchar(100)
	,game_name varchar(100)
	,play_level varchar(100)
	,rate int
);

insert into table_score values ('2023','ez2on','lv14',92);
insert into table_score values ('2023','ez2on','lv15',87);
insert into table_score values ('2023','ez2on','lv16',67);
insert into table_score values ('2023','ez2on','lv17',80);
insert into table_score values ('2023','ez2on','lv18',93);
insert into table_score values ('2023','ez2on','lv19',92);
insert into table_score values ('2023','ez2on','lv20',65);
insert into table_score values ('2023','djmax','lv14',88);
insert into table_score values ('2023','djmax','lv15',80);
insert into table_score values ('2023','djmax','lv16',93);
insert into table_score values ('2023','djmax','lv17',91);
insert into table_score values ('2023','djmax','lv18',89);
insert into table_score values ('2023','djmax','lv19',83);
insert into table_score values ('2023','djmax','lv20',99);

 

 

1. DECODE 혹은 CASE 문을 이용하는 방법

 가장 전통적인 방법이다. CASE 문은 거의 모든 db 벤더에 다 있기 때문에 CASE문을 사용했다. 

ORACLE이라면 DECODE를 SQL Server를 사용한다면 IIF를 사용하면 된다.

select 
	years
	,game_name
	,case when play_level ='lv14' then rate else 0 end 'lv14'
	,case when play_level ='lv15' then rate else 0 end 'lv15'
	,case when play_level ='lv16' then rate else 0 end 'lv16'
	,case when play_level ='lv17' then rate else 0 end 'lv17'
	,case when play_level ='lv18' then rate else 0 end 'lv18'
	,case when play_level ='lv19' then rate else 0 end 'lv19'
	,case when play_level ='lv20' then rate else 0 end 'lv20'
from table_score;

 

 

행이 칼럼으로 변환이 되다 만것 같다. 여기서 추가로 집계를 해보자.

우선 game_name, year 를 그룹화 해야되고 그리고 play_level에 대한것은 집계를 통해 그룹화 하도록 하자.

select 
years,game_name,sum(lv14),sum(lv15),sum(lv16),sum(lv17),sum(lv18),sum(lv19),sum(lv20)
from 
(select 
	years
	,game_name
	,case when play_level ='lv14' then rate else 0 end 'lv14'
	,case when play_level ='lv15' then rate else 0 end 'lv15'
	,case when play_level ='lv16' then rate else 0 end 'lv16'
	,case when play_level ='lv17' then rate else 0 end 'lv17'
	,case when play_level ='lv18' then rate else 0 end 'lv18'
	,case when play_level ='lv19' then rate else 0 end 'lv19'
	,case when play_level ='lv20' then rate else 0 end 'lv20'
from table_score 
) a
group by years,game_name

그럼 칼럼을 로우로 정상적으로 바꾼것처럼 출력이 가능하다.

---

2.WITH절을 이용한 방법

with game_rate as
(select 
	years
	,game_name
	,case when play_level ='lv14' then rate else 0 end 'lv14'
	,case when play_level ='lv15' then rate else 0 end 'lv15'
	,case when play_level ='lv16' then rate else 0 end 'lv16'
	,case when play_level ='lv17' then rate else 0 end 'lv17'
	,case when play_level ='lv18' then rate else 0 end 'lv18'
	,case when play_level ='lv19' then rate else 0 end 'lv19'
	,case when play_level ='lv20' then rate else 0 end 'lv20'
from table_score 
)
select years,game_name,sum(lv14),sum(lv15),sum(lv16),sum(lv17),sum(lv18),sum(lv19),sum(lv20)
from game_rate
group by years,game_name

사실상 CASE 문을 이용한 방법과 같다. 서브쿼리 대신 WITH절을 사용하는것이다. 결과는 동일하다. 서브쿼리를 사용하는것보다 가독성을 약간 더 좋게 할 수 있다. 

---

3.PIVOT함수를 사용하는 방법

select * 
from (select 
         years
        ,game_name
        ,play_level
        ,rate 
       from table_score) 
pivot(
	sum(rate) 
	for play_level in('lv14','lv15','lv16','lv17','lv18','lv19','lv20')
);

PIVOT 함수에 대한 설명을 하자면 다음과 같다.

select ...
FROM (피벗 대상 SELECT 문)
PIVOT( 집계함수(위의 FORM절에 있는 칼럼) 
       FOR 피벗대상 칼럼 IN( 칼럼 으로 올릴 피벗값 리스트)
     )

PIVOT 함수를 사용해 집계할 칼럼값과 칼럼으로 분리할 항목을 FOR 칼럼 IN(칼럼 값1, 칼럼값2....) 식으로 나열하면

나열된 각각의 값들이 칼럼으로 변환된다. 그리고 집계함수의 결과가 칼럼으로 변환된 칼럼 의 값이 된다.

피벗 대상 select 문의 칼럼	칼럼으로 올릴 피벗값1	칼럼으로 올릴 피벗값2	칼럼으로 올릴 피벗값3
칼럼 값	집계함수(위의 FORM절에 있는 칼럼)	집계함수(위의 FORM절에 있는 칼럼)	집계함수 (위의 FORM절에 있는 칼럼)