PIVOT

SQL Server

PIVOT(aggregate_function(column_to_aggregate)FOR column_to_transformIN (list_of_values))

• aggregate_function(column_to_aggregate): This represents the aggregate function, such as COUNT, SUM, AVG, MIN, MAX, etc., applied onto the column that needs to be aggregated.
• column_to_transform: This identifies the column in the original table that will be transformed or pivoted into the columns of the final, pivoted table.
• list_of_values: This refers to the unique values in the column_to_transform which will become the new column headers in the final, pivoted table.

Example

SELECT Store, [2018], [2019], [2020]
FROM (
    SELECT Store, Year, Sales
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Year IN ([2018], [2019], [2020])
) AS PivotTable;

Output

| Store | 2018 | 2019 | 2020 |
|-------|------|------|------|
| A     | 100  | 120  | 150  |
| B     | 200  | 180  | 210  |
| C     | 300  | 320  | 350  |

Explanation

The provided SQL script performs a pivot operation on the SalesData source table, which transforms rows into columns. Here, SUM of sales is being calculated for each year (2018, 2019, 2020) across each Store. The pivot table in the output shows sales data for the stores across different years.

Oracle

SELECT …FROM …PIVOT (aggregate_function(column_to_aggregate) FOR column_to_transform IN ( list_of_values))WHERE …

• select: Used to specifically mention the columns that are to be returned in the result set from the source table.
• from: Specifies the source table from where the data needs to be extracted for the pivot operation.
• pivot: Pivot clause is used to rotate the data into a tabular format.
• aggregate_function(column_to_aggregate): It is a mathematical function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement. It’s applied to the column_to_aggregate.
• column_to_transform: This is the column that gets transformed during the pivot operation. It turns unique column values into new column names in the output result set.
• in ( list_of_values): Specifies the values that are to be utilized for creating the new columns in the pivoted result set.
• where: Used to filter the result set based on a condition applied to the rows in the source table. Only rows that meet the condition take part in the pivot operation.

Example

SELECT * FROM (
SELECT deptno, ename, sal FROM emp)
PIVOT (SUM(sal) FOR deptno IN (10 AS ten, 20 AS twenty, 30 AS thirty));

Output

+------+--------+--------+--------+
| ENAME | TEN    | TWENTY | THIRTY |
+------+--------+--------+--------+
| SMITH | (NULL) | 800    | (NULL) |
| ALLEN | (NULL) | (NULL) | 1600   |
| WARD  | (NULL) | (NULL) | 1250   |
| JONES | (NULL) | 2975   | (NULL) |
| MARTIN| (NULL) | (NULL) | 1250   |
| BLAKE | (NULL) | (NULL) | 2850   |
| CLARK | 2450   | (NULL) | (NULL) |
| SCOTT | (NULL) | 3000   | (NULL) |
| KING  | 5000   | (NULL) | (NULL) |
| TURNER| (NULL) | (NULL) | 1500   |
| ADAMS | (NULL) | 1100   | (NULL) |
| JAMES | (NULL) | (NULL) | 950    |
| FORD  | (NULL) | 3000   | (NULL) |
| MILLER| 1300   | (NULL) | (NULL) |
+------+--------+--------+--------+

Explanation

The Oracle PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. In the example, the PIVOT operation is being used to rotate the employee subquery’s output by converting unique DEPTNO values into new columns in the output and aggregating the corresponding SAL values.