[Postgresql] 세로 테이블을 가로 테이블로 pivot하기 (crosstab)

1. extension 설치

CREATE EXTENSION tablefunc;

 

2. crosstab 사용

-  ID (=여기선 title) 기준 연도별 총액 산출 

- 세로 테이블을 가로 테이블로 Pivot

- 동적 컬럼을 위해 권장되지 않지만 values 및 as 값들을 셋팅해서 쿼리 생성

- 합계를 하단 마지막으로 보내기 위해 order by 설정.

- crosstab select 문은 3개의 컬럼을 반환함.(컬럼1: ID, 컬럼2: 테이블 범주(카테고리), 컬럼3: 각 셀에 할당 될 값 )  

- as 를 통해 컬럼명 재지정을 할 때 데이터 타입 유의

SELECT *
FROM crosstab(
 'select title, plan_year, total_cost
  from (
    select  unnest(array[''항목1'', ''항목2'', ''항목3'', ''항목4'', ''항목5'', ''합계'']) AS title,
            unnest(array[plan_year, plan_year, plan_year, plan_year, plan_year, plan_year]) AS plan_year,
            unnest(array[a_cost, c_cost, e_cost, m_cost, i_cost, dmnd_cost ]) AS total_cost
    from anls_inv_plan 
    order by plan_year
  ) aip
  order by 1,2'
  ,$$VALUES ('2022'), ('2023'), ('2024'), ('2025'), ('2026'), ('2052')$$
 ) AS ct (
   title text
 , "2022" numeric
 , "2023" numeric
 , "2024" numeric
 , "2025" numeric
 , "2026" numeric
 , "2052" numeric
 )
 order by case when title ='합계' then 1 else 0 end;

- 실행결과