Oracle Pivot, UnPivot

■ Pivot - 행을 열로 변환

# Sample Query - 복사, 붙여 넣기로 테스트

select ymd, nvl(총무과, 0) as 총무과, nvl(인사과, 0) as 인사과, nvl(전산실, 0) as 전산실
  from (select a.ymd, a.dept, count(1) as cnt
              from (select '20230901' as ymd, '총무과' as dept from dual
                         union all
                         select '20230901' as ymd, '인사과' as dept from dual
                         union all
                         select '20230902' as ymd, '전산실' as dept from dual
                         union all
                         select '20230902' as ymd, '총무과' as dept from dual
                         union all
                         select '20230903' as ymd, '인사과' as dept from dual) a
            group by a.ymd, a.dept) a
            pivot (sum(cnt) for dept in ('총무과' as 총무과, '인사과' as 인사과, '전산실' as 전산실))
 order by ymd

Pivot 결과

■ UnPivot

# Sample Query - 복사, 붙여 넣기로 테스트

select *

 from (select 11 as apple, 2 as lemon, 17 as grape from dual
           union all

           select 5 as apple, 7 as lemon, 12 as grape from dual

           union all

           select 8 as apple, 6 as lemon, 7 as grape from dual

           union all

           select 9 as apple, 2 as lemon, 9 as grape from dual

           union all

           select 3 as apple, 1 as lemon, 1 as grape from dual)
unpivot (count for fruit in (apple, lemon, grape))

order by fruit

UnPivot 결과