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!
