Pivot/Unpivot Examples (with multi-column UNPIVOT)

Hey fellow database enthusiasts,


Long time no post...


I figured I would post some examples of "PIVOT" and "UNPIVOT" because I feel like the Oracle documentation doesn't give enough.


I've constructed some of my own - which includes a "multi-column" UNPIVOT operation as well.


Take these and try them on your own in your own database - see if you can apply the techniques used in the examples with real data as well...


Here goes:


/* ------------------------------------------------------------------- */
-- Pivot Example (rows-to-columns)
WITH row_based_table AS (
SELECT 1 AS ID, 'jack' AS code, 123.33 AS sales, 999 AS units FROM dual
UNION ALL
SELECT 1 AS ID, 'tom' AS code, 456.44 AS sales, 199 AS units FROM dual
UNION ALL
SELECT 1 AS ID, 'bob' AS code, 789.55 AS sales, 100 AS units FROM dual
)
SELECT *
  FROM row_based_table
PIVOT
(SUM (sales) AS sales
,SUM (units) AS units
FOR code IN ('jack' AS jack, 'tom' AS tom, 'bob' AS bob)
);
/* ------------------------------------------------------------------- */
-- Multi-Column Un-Pivot Example (columns-to-rows)
WITH column_based_table AS (
SELECT 1       AS ID
    , 'jack'   AS guy_a
    , 'jill'   AS gal_a
    --
    , 'tom'    AS guy_b
    , 'tanya'  AS gal_b
    --
    , 'bob'    AS guy_c
    , 'belinda'AS gal_c
  FROM dual
)
SELECT *
  FROM column_based_table
   UNPIVOT EXCLUDE NULLS ((guy, gal)
     FOR couple IN (
       (guy_a, gal_a) AS 'a couple'
     , (guy_b, gal_b) AS 'b couple'
     , (guy_c, gal_c) AS 'c couple'
    )
);
/* ------------------------------------------------------------------- */
-- Un-Pivot Example (columns-to-rows)
WITH column_based_table AS (
SELECT 1 AS ID, 'jack' AS A, 'tom' AS b, 'bob' AS c FROM dual
)
SELECT *
  FROM column_based_table
   UNPIVOT EXCLUDE NULLS (val
     FOR code IN (
       a AS 'a column'
     , b AS 'b column'
     , c AS 'c column'
    )
);


Take care!