Understanding how to transform rows into columns (PIVOT) and columns into rows (UNPIVOT) in SQL can greatly simplify complex data analysis and reporting. Both PIVOT and UNPIVOT rotate data from its original structure – whether that’s from rows into columns or vice versa. Mastering these SQL commands provides flexibility in shaping data to meet diverse business needs.
Introduction to PIVOT and UNPIVOT
The PIVOT and UNPIVOT relational operators in SQL allow you to dynamically rotate data between row and column orientation.
PIVOT rotates unique values from multiple rows into multiple columns. For example, you may have a table containing sales data by product, with each product in its own row. Using PIVOT, this product data can be turned into separate product columns to allow for easy side-by-side comparison.
Conversely, UNPIVOT does the opposite – rotating unique columns into rows. If you pivoted the sales data by product into columns, UNPIVOT could switch it back into separate product rows again.
Understanding when to use PIVOT vs UNPIVOT depends on the business needs. Both transform the layout of a table for easier querying, aggregation, and analysis.
When to Use PIVOT and UNPIVOT
Some common reasons for using PIVOT and UNPIVOT include:
- Turning multiple rows of related data into columns for comparison across fields. PIVOT shines here.
- Converting back to rows after pivoting into columns. UNPIVOT fits for switching pivoted data back to normal row format.
- Rotating between detailed and aggregated views of data through pivoting and unpivoting.
- Reorganizing data initially imported in an unoptimized column format into easier-to-query rows. UNPIVOT helps.
- Reducing repetitive querying by pivoting data once for a dashboard or report instead of per-query.
Understanding that PIVOT/UNPIVOT transforms horizontal to vertical orientation (or vice versa) clarifies when to standardize on one layout or alternate between them.
PIVOT Syntax and Examples
PIVOT requires a few key parameters: the aggregation column, the pivot column, and pivot value columns.
Here is an example PIVOT syntax:
SELECT <aggregation column>,
[<pivot column>],
[<pivot value columns>]
FROM <table>
PIVOT
(
<aggregate function>(<aggregation column>) -- What to aggregate
FOR [<pivot column>] IN (<pivot values>) -- Unique values to become columns
) AS <alias>;
Breaking this down:
- The aggregation column is the field to aggregate – like summing sales
- The pivot column contains the unique values to turn into columns – such as products
- Pivot value columns are those unique values that end up as column headers
- An aggregate function (like SUM) specifies how to aggregate the data
For example, pivoting total sales by product:
SELECT Year,
Product,
[Paper] AS PaperSales,
[Pens] AS PensSales,
[Pencils] AS PencilsSales
FROM SalesData
PIVOT
(
SUM(SalesTotal)
FOR Product IN ([Paper], [Pens], [Pencils])
) AS PivotSales;
Results:
| Year | PaperSales | PensSales | PencilsSales |
|---|---|---|---|
| 2020 | $5,000 | $3,000 | $2,000 |
| 2021 | $6,000 | $3,500 | $2,500 |
This pivoted the separate product rows into product columns with aggregated sales totals.
UNPIVOT reverses this operation – see next section.
UNPIVOT Syntax and Examples
While PIVOT rotates unique rows into columns, UNPIVOT flips unique columns back into rows.
UNPIVOT syntax looks like:
SELECT <other columns>,
<unpivot column>,
<unpivot value column>
FROM <table>
UNPIVOT
(
<unpivot value column> FOR <unpivot column> IN (<column list>)
) AS <alias>;
In this case:
- Other columns are those fields not being unpivoted, like dates
- The unpivot column contains the field to populate with old column names (like products)
- Unpivot value column gets the values from those former columns (such as sales totals)
Using the previous pivoted data, UNPIVOT would switch it back to rows:
SELECT Year,
Product,
Sales
FROM PivotSales
UNPIVOT
(
Sales FOR Product IN (PaperSales, PensSales, PencilsSales)
) AS UnpivotSales;
Results:
| Year | Product | Sales |
|---|---|---|
| 2020 | Paper | $5,000 |
| 2020 | Pens | $3,000 |
| 2020 | Pencils | $2,000 |
| 2021 | Paper | $6,000 |
| 2021 | Pens | $3,500 |
| 2021 | Pencils | $2,500 |
UNPIVOT pulled apart the pivoted columns back into the original rows layout.
Performance Considerations for PIVOT and UNPIVOT
Both PIVOT and UNPIVOT can appear slow since they dynamically transform data at query time. Performance depends on table size, indexes, and other factors.
To optimize PIVOT/UNPIVOT speed:
- Index columns used for pivoting, like product categories
- Specify exact column names instead of using * wildcards
- Filter data first with a WHERE clause before transforming
- Test performance with small sample data sets
- Tune server memory, cores, resources for complex operations
Properly indexing source tables boosts performance for ad hoc pivoting. Filtering large data before pivoting also helps responsiveness.
In some cases, persisting transformed data in separate tables queried by reports may be preferable to pivoting dynamically per request. Testing different approaches identifies the ideal balance.
The query planner’s ability to leverage indexes and other performance features also impacts PIVOT speed. Upgrading to modern, enterprise SQL engines facilitates operating on big data.
FAQ
Common questions around SQL PIVOT and UNPIVOT include:
What is the difference between PIVOT and UNPIVOT in SQL?
PIVOT rotates unique row values into columns, aggregating multiple rows together. UNPIVOT does the reverse – rotating unique columns back into rows instead.
When should I use PIVOT vs UNPIVOT?
Use PIVOT to transform row data into columns for comparison across fields. UNPIVOT switches column data back to rows after pivoting. Decide based on the desired data layout.
What databases support PIVOT and UNPIVOT?
PIVOT and UNPIVOT are supported in SQL Server, Oracle, PostgreSQL, and other advanced enterprise databases. Check documentation for specific syntax details by product.
Conclusion
SQL’s PIVOT and UNPIVOT commands empower dynamically restructuring data between row and column orientation. Extracting, reporting, and analyzing data becomes easier through pivoting heterogeneous information onto common columns. Performance tuning and testing helps balance speed and flexibility for large datasets.
Whether pivoting data once for a dashboard or alternating layouts through code, mastering PIVOT and UNPIVOT unlocks new possibilities for answering business questions. By understanding these versatile SQL features, experienced developers and analysts gain a valuable advantage working with diverse data.
