19.1.4. Pivoting and Unpivoting Data

• Transform rows into columns and vice versa.

Pivoting is a technique used in data manipulation, particularly in SQL databases, where you can change data orientation. Instead of displaying data in rows, you can turn these rows into columns, thereby summarizing and condensing information. This transformation helps in presenting data more effectively, especially in reporting contexts. For instance, if you have sales data for different quarters, pivoting can allow you to summarize this data in a way that each quarter’s revenue appears in its own column.

Imagine you have a group of students and their scores in different subjects listed vertically. Instead of reading through the rows to get each student's scores, you rearrange this information so that each student's name is on one row, with their scores in columns for each subject. This pivoted table is much easier to read at a glance.

• Pivoting:

SELECT *
FROM (
    SELECT year, quarter, revenue FROM sales
) AS src
PIVOT (
    SUM(revenue) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;

In this SQL example, the pivot operation is performed on sales data to summarize revenue by quarters. The inner query selects year, quarter, and revenue from a 'sales' table, while the PIVOT clause aggregates the revenue data into separate columns for Q1, Q2, Q3, and Q4. This allows us to see total revenue for each quarter side-by-side for each year, making comparison and analysis straightforward.

Think of a company that tracks quarterly sales over several years. If they want to present their performance clearly, they could create a summary table using pivoting. Instead of having a long list of sales transactions, with months and years spread over rows, they convert this into a summary where each column represents a quarter. This helps stakeholders quickly understand performance trends over the year.

--