How do you use the pivot function in Oracle?

2 years ago
Isabella Edwards
2 minutes

The PIVOT function in Oracle database is used to convert rows into columns. Here is how the PIVOT function is used:

Suppose there is a table named sales, which includes the following columns:

  1. year: the specific calendar time period
  2. quarter: a period of three months
  3. sales revenue

To display sales figures with quarters as column names, years as row names, and sales amounts as values, you can use the PIVOT function like this:

SELECT *
FROM (
    SELECT year, quarter, amount
    FROM sales
)
PIVOT (
    SUM(amount)
    FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
)
ORDER BY year;

In the above statement, start by selecting the year, quarter, and amount columns from the sales table. Next, pivot the quarter column as the column name and the amount column as the value for aggregation, specifying the quarters to display in the IN clause. Finally, sort by year.

To perform aggregation operations on other columns, you can replace the SUM function with other aggregation functions such as AVG, MAX, etc.

It is important to note that the PIVOT function can only be used for known column values when writing a query, and it cannot dynamically generate columns. If dynamic column generation is needed, consider using dynamic SQL to achieve this.

bannerAds