Transforming presentation: What is a Pivoting operation?
- It is a key technique in DWH: allow you to transform multiple rows of input into fewer and generally wider rows
- Allow to transform the presentation of the data, rotating rows into columns and presenting data in a crosstabular format, or vice-versa.
- When pivoting, an aggregation operator is applied for each item in the pivot column value list
- Use the
pivot_clause
of the SELECT
statement to write crosstabulation queries that rotate rows into columns, aggregating data in the process of the rotation.
- Pivoting: Transactional format <=> Crosstabular (relational) format
Consider the example below: - The view SALES_VIEW displays data in a transactional format
- It lists amount and quantity sold by product, country, quarter and channel.
- In this case, a product may be sold through various channels in a given country and a given quarter of year.
- If you need to see the sales by channel_id on a relational or crosstabular format, you can use a pivoting operation:
SQL> CREATE VIEW sales_view AS
SELECT
prod_name product, country_name country, channel_id channel,
SUBSTR(calendar_quarter_desc, 6,2) quarter,
SUM(amount_sold) amount_sold, SUM(quantity_sold) quantity_sold
FROM sales, times, customers, countries, products
WHERE sales.time_id = times.time_id AND
sales.prod_id = products.prod_id AND
sales.cust_id = customers.cust_id AND
customers.country_id = countries.country_id
GROUP BY prod_name, country_name, channel_id,
SUBSTR(calendar_quarter_desc, 6, 2);
- Using the PIVOT clause you can reconstruct the data on a cross tabular form:
SQL> SELECT * FROM
(SELECT product, channel, amount_sold
FROM sales_view
WHERE country = 'United States of America'
) S PIVOT (SUM(amount_sold)
FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,
5 AS CATALOG_SALES, 9 AS TELESALES))
ORDER BY product;
PRODUCT DIRECT_SALES INTERNET_SALES CATALOG_SALES TELESALES
------------------------- ---------------------- ---------------------- ---------------------- ------------------
...
Home Theatre Package... 2052150.1 310431.22
Internal 6X CD-ROM 135284.16 15839.73
Internal 8X CD-ROM 158157.23 24398.22
Keyboard Wrist Rest 110554 22477.41 1354.87
...
71 rows selected