Pivot, Unpivot in Oracle SQL

Below is a sample table.

select * from olympic_medal_winners;

Pivot: Convert rows to columns.

select * from (
select noc,medal from olympic_medal_winners)
pivot (
  count(*) for medal
  in ( 'Gold' Gold, 'Silver' Silver , 'Bronze'   Bronze)

)
order by 1 desc
fetch first 6 rows only;

Note: Always maintain pivot in outer most subquery with "select * from" clause.

Below is a query which results total events,sports, gender where gold , silver, bronze is won.

select * from (
 select noc, medal, sport, event, gender
 from   olympic_medal_winners
)
pivot (
 count(distinct sport ||'#'|| event ||'#'||gender )
 for medal in ( 'Gold' gold, 'Silver' silver, 'Bronze' bronze )
)
order  by 2 desc, 3 desc, 4 desc
fetch first 5 rows only;

Note: The outer grouping will occur only if that column is not used in Pivot braces. In above case sport,gender, event columns are used in Pivot braces() so, that wouldn't be grouped in result. Only NOC column is used to group.

Using multiple measure(count, sum) and filter pivoted data.

select * from (
  select noc, medal, sport, event, gender, athlete
  from   olympic_medal_winners
)
pivot  (
  count( distinct sport ||'#'|| event ||'#'|| gender ) medals,
  count( distinct sport ) sports,
  listagg( athlete, ',') within group (order by athlete) athletes
  for medal in ( 'Gold' gold )
)

where  noc like 'D%'

Unpivot: Convert Columns to rows.

Below is an another data set representing medals count.


select * from olympic_medal_tables

Below is an example of unpivoting the data.
select * from olympic_medal_tables
unpivot (medal_count for medal_colour in (
  gold_medals as 'GOLD',
  silver_medals as 'SILVER',
  bronze_medals as 'BRONZE'
))
order  by noc
fetch  first 6 rows only;

NOC  MEDAL_COLOUR  MEDAL_COUNT  
ALG  GOLD                                  0            
ALG  BRONZE                             0            
ALG  SILVER                                2            
ARG  GOLD                                  3            
ARG  BRONZE                             0            
ARG  SILVER                                1

Note: Observe that each column is denoted with a data set name GOLD, SILVER, BROZE etc to consider each of them in one column.

Generating Calendar using Connect Prior by

Note: Replace $Start_dt, $End_dt with the actual start,end dates

SELECT

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'Day') DAY_NAME,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM) DAY_DT,

            TO_NUMBER(TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') ) CAL_WEEK_NUM,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') CAL_WEEK_START_DT,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') + 6 CAL_WEEK_END_DT,

            TO_NUMBER(TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'DDD') ) CAL_DAY_OF_YEAR,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM) - TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'Q'

) CAL_DAY_OF_QTR,

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'DD') CAL_DAY_OF_MONTH,

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'D') CAL_DAY_OF_WEEK

        FROM

            DUAL

        CONNECT BY

            ROWNUM <= TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - TO_DATE('$Start_Dt','MM/DD/YYYY HH24:MI:SS')

        ORDER BY

            DAY_DT;