본문 바로가기
카테고리 없음

[SQL 중급] 25. 피벗 테이블 (PIVOT) 조회

by 꾸리_No9 2024. 4. 5.
SQL 피벗 테이블 :
inline-view:  xy - pivot : x  

 

SQL로 피벗 테이블 만드는 방법 : 

1)  XY 값을 조회한다

2)  XY값을 From 절 서브쿼리로 넣는다 

3)  Pivot 라인에 들어오는 X값은 모두 나열해야 한다.

    → pivot ( count(*) for  x절  in ( 'x1' , 'x2', 'x3' , 'x4' , 'x5' )

 

 

1. Pivot  :

 - X : Team_id , Y : Birth_year, 값: Count

select *
from  ( select team_id, substr(birth_date,1,2) as birth_year from player)
pivot ( count(*)  for team_id  in ('K01','K02','K03','K04','K05','K06','K07','K08','K09','K10'))
order by birth_year ;

 

 

2. Pivot  :

 - X : Birth_year,  Y  : Team_id ,  값: Count

select *
from ( select team_id, substr(birth_date,1,2) as birth_year from player)
pivot (count(*) for birth_year in ('37', '44', '46', '49', '51', '54', '58', '59', '60', '61', '63', '65', '68',
                                                   '71', '72', '74', '75', '76', '77', '78', '79', '80', '83'));

 

 

 

3. Case when을 사용한 유사 피봇 :

 - X : Team_id , Y : Birth_year, 값: Count

 

select substr(birth_date,1,2) as birth_year,
           count(case when team_id = 'K01' then 1 end ) as "K01",
           count(case when team_id = 'K02' then 1 end ) as "K02",
           count(case when team_id = 'K03' then 1 end ) as "K03",
           count(case when team_id = 'K04' then 1 end ) as "K04",
           count(case when team_id = 'K05' then 1 end ) as "K05",
           count(case when team_id = 'K06' then 1 end ) as "K06",
           count(case when team_id = 'K07' then 1 end ) as "K07",
           count(case when team_id = 'K08' then 1 end ) as "K08",
           count(case when team_id = 'K09' then 1 end ) as "K09",
           count(case when team_id = 'K10' then 1 end ) as "K010"

from player
group by substr(birth_date,1,2)
order by birth_year;