select * from co.orders 
fetch first 20 rows only

Pivot the number of orders for customers 1, 2, and 3

select * from co.orders   
pivot  (   
  count(*)    
  for customer_id    
  in ( 1, 2, 3 )   
) 
fetch first 20 rows only

Fitlering pivot output

select * from co.orders  
pivot  (  
  count(*)   
  for customer_id   
  in ( 1, 2, 3 )  
)  
where  "1" > 0 or "2" > 0 or "3" > 0 

Remove the implicit grouping

with rws as (  
  select customer_id from co.orders  
  where  customer_id in ( 1, 2, 3 )  
)  
  select * from rws  
  pivot  (  
    count(*)   
    for customer_id   
    in ( 1 as "CUST_1", 2 as "CUST_2", 3 as "CUST_3" )  
  )

Expressions disallowed for pivot column

select * from co.orders  
pivot  (  
  count(*)   
  for to_char ( order_datetime, 'MON' )  
  in ( 'Jan', 'Feb', 'Mar' )  
)

Pivoting on an expression

with order_totals as (  
  select to_char ( o.order_datetime, 'MON' ) order_month  
  from   co.orders o  
)  
  select * from order_totals  
  pivot (  
    count (*)  
    for order_month in (  
      'JAN' JAN, 'FEB' FEB, 'MAR' MAR, 'APR' APR, 'MAY' MAY, 'JUN' JUN  
    )  
  )

Adding implicit grouping

with order_totals as (  
  select extract ( year from o.order_datetime ) order_year,  
         to_char ( o.order_datetime, 'MON' ) order_month  
  from   co.orders o  
)  
  select * from order_totals  
  pivot (  
    count (*)  
    for order_month in (  
      'JAN' JAN, 'FEB' FEB, 'MAR' MAR, 'APR' APR, 'MAY' MAY, 'JUN' JUN  
    )  
  )  
--  group by order_year -- this is implicit 
  order by order_year

Pivoting multiple aggregations

with order_totals as (  
  select extract ( year from o.order_datetime ) order_year,  
         to_char ( o.order_datetime, 'MON' ) order_month,  
         customer_id  
  from   co.orders o  
)  
  select * from order_totals  
  pivot (  
    count (*) orders,  
    count ( distinct customer_id ) customers  
    for order_month in (  
      'JAN' JAN, 'FEB' FEB, 'MAR' MAR  
    )  
  )  
  order by order_year

Pivoting multiple columns

with order_totals as (  
  select extract ( year from o.order_datetime ) order_year,  
         to_char ( o.order_datetime, 'MON' ) order_month,  
         customer_id  
  from   co.orders o  
)  
  select * from order_totals  
  pivot (  
    count (*) orders  
    for ( customer_id, order_month ) in (  
      ( 1, 'JAN' ) jan_cust_1,   
      ( 1, 'FEB' ) feb_cust_1,   
      ( 2, 'FEB' ) feb_cust_2,   
      ( 3, 'MAR' ) mar_cust_3  
    )  
  )  
  order by order_year

Dynamic pivoting

with order_totals as (  
  select extract ( year from o.order_datetime ) order_year,  
         to_char ( o.order_datetime, 'MON' ) order_month  
  from   co.orders o  
)  
  select * from order_totals  
  pivot (  
    count (*) orders  
    for order_month in (  
      select to_char ( add_months ( sysdate, level - 1 ), 'MON' )   
      from   dual   
      connect by level <= 3  
    )  
  )

Dynamic XML pivoting

with order_totals as (  
  select extract ( year from o.order_datetime ) order_year,  
         to_char ( o.order_datetime, 'MON' ) order_month  
  from   co.orders o  
)  
  select xmlserialize ( document order_month_xml indent size = 2 )  
  from   order_totals  
  pivot xml (  
    count (*) orders  
    for order_month in (  
      select to_char ( add_months ( sysdate, level - 1 ), 'MON' )   
      from   dual   
      connect by level <= 3  
    )  
  )

Dynamic pivoting with fixed columns

with order_totals as (  
  select extract ( year from o.order_datetime ) order_year,  
         dense_rank () over ( -- careful with row_number() vs rank() vs dense_rank()!  
           partition by extract ( year from o.order_datetime )  
           order by to_char ( o.order_datetime, 'YYYYMM' )   
         ) rn  
  from   co.orders o  
)  
  select * from order_totals  
  pivot (  
    count (*) orders  
    for rn in ( 1, 2, 3 )  
  )

Dynamic pivoting with fixed columns and metadata

with order_totals as (   
  select extract ( year from o.order_datetime ) order_year,   
         to_char ( o.order_datetime, 'MON' ) order_month,   
         dense_rank () over ( -- careful with row_number() vs rank() vs dense_rank()!   
           partition by extract ( year from o.order_datetime )   
           order by to_char ( o.order_datetime, 'YYYYMM' )    
         ) rn   
  from   co.orders o   
)   
  select * from order_totals   
  pivot (   
    count (*) orders, max ( order_month ) mth   
    for rn in ( 1, 2, 3 )   
  )

Dynamic pivoting using dynamic SQL

declare  
  pivot_values clob;  
  stmt         clob;  
  cur          sys_refcursor; 
  output_json  varchar2(100); 
begin  
  select listagg (   
           '''' || to_char ( add_months ( sysdate, level - 1 ), 'MON' ) || '''', ','  
         ) within group ( order by 1 )  
  into   pivot_values  
  from   dual   
  connect by level <= 3;  
    
  stmt := q'!  
  with order_totals as (  
  select extract ( year from o.order_datetime ) order_year,  
         to_char ( o.order_datetime, 'MON' ) order_month  
  from   co.orders o  
)  
  select json_object ( * )  from order_totals  
  pivot (  
    count (*) orders  
    for order_month in ( !' || pivot_values || ' )  
  )';  
    
  dbms_output.put_line ( stmt );  
    
  open cur for stmt; 
  loop 
    fetch cur into output_json; 
    exit when cur%notfound; 
     
    dbms_output.put_line ( output_json );  
  end loop;  
  close cur; 
  
end;  

Add row and column total columns

with order_totals as (  
  select extract ( year from o.order_datetime ) order_year,  
         to_char ( o.order_datetime, 'MON' ) order_month  
  from   co.orders o  
)  
  select order_year, sum ( jan ), sum ( feb ), sum ( mar ),   
         sum ( jan ) + sum ( feb ) + sum ( mar ) year_tot  
  from   order_totals  
  pivot (  
    count (*)  
    for order_month in (  
      'JAN' JAN, 'FEB' FEB, 'MAR' MAR  
    )  
  )  
  group by rollup ( order_year )  
  order by order_year

Add row and column total columns - v2

with order_totals as (  
  select count (*) orders,  
         case grouping_id ( to_char ( o.order_datetime, 'MON' ) )   
           when 1 then 'Total'  
           else to_char ( o.order_datetime, 'MON' )   
         end order_month,  
         case grouping_id ( extract ( year from o.order_datetime ) )  
           when 1 then 'Total'  
           else to_char ( extract ( year from o.order_datetime ) )  
         end order_year  
  from   co.orders o  
  group  by rollup ( extract ( year from o.order_datetime ) ),  
         rollup ( to_char ( o.order_datetime, 'MON' ) )  
           
)  
  select * from order_totals  
  pivot (  
    sum ( orders )  
    for order_month in (  
      'JAN' JAN, 'FEB' FEB, 'MAR' MAR, 'Total' tot  
    )  
  )  
  order by order_year

create table year_month_sales as 
  with order_totals as ( 
    select extract ( year from o.order_datetime ) order_year, 
           to_char ( o.order_datetime, 'MON' ) order_month 
    from   co.orders o 
  ) 
    select * from order_totals 
    pivot ( 
      count (*) 
      for order_month in ( 
        'JAN' JAN, 'FEB' FEB, 'MAR' MAR, 'APR' APR, 'MAY' MAY, 'JUN' JUN, 
        'JUL' JUL, 'AUG' AUG, 'SEP' SEP, 'OCT' OCT, 'NOV' NOV, 'DEC' DEC 
      ) 
    ) 
    order by order_year

select * from year_month_sales

Columns to rows

select * from year_month_sales   
unpivot (   
  orders   
  for sale_month    
  in ( jan, feb, mar )   
)

Unpivoting columns with different data types

select * from co.orders  
unpivot (  
  col_value  
  for col_name   
  in ( order_datetime, customer_id, order_status, store_id )  
)

Unpivoting columns with different data types

with rws as (   
  select order_id,    
         to_char ( order_datetime, 'yyyy-mm-dd' ) order_datetime,    
         to_char ( customer_id ) customer_id ,    
         order_status,    
         to_char ( store_id ) store_id   
  from   co.orders   
)   
  select * from rws   
  unpivot (   
    col_value   
    for col_name    
    in ( order_datetime, customer_id, order_status, store_id )   
  ) 
  fetch first 20 rows only

Unpivot many columns

with rws as (  
  select order_year, jan, feb, mar, apr from year_month_sales  
)  
  select * from rws  
  unpivot (  
    ( month_1, month_2 )  
    for sale_month  
    in ( ( jan, feb ), ( mar, apr ) as 'Mar Apr' )  
  )

Tranpose rows and columns

select * from year_month_sales  
unpivot (  
  orders  
  for sale_month   
  in (   
    jan, feb, mar, apr, may, jun,   
    jul, aug, sep, oct, nov, dec   
  )  
)  
pivot (  
  sum ( orders )   
  for order_year in (  
    2018, 2019  
  )  
)