In SQL, you can pivot data from rows to columns using the PIVOT operator (if your SQL dialect supports it) or by using conditional aggregation. Here’s how you can do both:
1. Using the PIVOT Operator (SQL Server Example)
The PIVOT operator is available in SQL Server and allows you to transform unique values from one column into multiple columns in the result set.
Example
Imagine you have a table Sales with the following structure:
| Product | Year | Amount |
|---------|------|--------|
| A | 2020 | 100 |
| A | 2021 | 150 |
| B | 2020 | 200 |
| B | 2021 | 250 |
You want to pivot the data to show the sales amounts for each product per year.
SELECT *
FROM Sales
PIVOT (
SUM(Amount) -- Aggregate function
FOR Year IN ([2020], [2021]) -- Columns to pivot to
) AS PivotTable;
This query will return:
| Product | 2020 | 2021 |
|---------|------|------|
| A | 100 | 150 |
| B | 200 | 250 |
2. Using Conditional Aggregation (Standard SQL)
If your SQL dialect does not support the PIVOT operator, you can achieve the same result using conditional aggregation with CASE statements.
Example
Using the same Sales table, you can write a query like this:
SELECT
Product,
SUM(CASE WHEN Year = 2020 THEN Amount ELSE 0 END) AS [2020],
SUM(CASE WHEN Year = 2021 THEN Amount ELSE 0 END) AS [2021]
FROM Sales
GROUP BY Product;
This will yield the same result:
| Product | 2020 | 2021 |
|---------|------|------|
| A | 100 | 150 |
| B | 200 | 250 |
Summary
- Use PIVOT if your SQL dialect supports it (like SQL Server).
- Use conditional aggregation with CASE statements for a more standard SQL approach, which works across most SQL databases.