How do you pivot data in SQL?

Pivoting data in SQL transforms rows into columns, typically to summarize or reorganize data for reporting. The process involves aggregating values based on a category column and displaying them as separate columns. While SQL doesn’t have a universal “PIVOT” keyword, common approaches include using conditional aggregation with CASE statements or leveraging database-specific functions like PIVOT in SQL Server or crosstab in PostgreSQL.

For example, consider a sales table with columns year, product, and revenue. To pivot the data to show revenue per product as columns for each year, you could use conditional aggregation:

SELECT 
 year,
 SUM(CASE WHEN product = 'A' THEN revenue ELSE 0 END) AS product_A,
 SUM(CASE WHEN product = 'B' THEN revenue ELSE 0 END) AS product_B
FROM sales
GROUP BY year;

This query creates a column for each product by checking its value in each row and summing the revenue. The GROUP BY ensures results are grouped by year. This method works in most SQL dialects but becomes cumbersome with many categories, as each requires a separate CASE statement.

In SQL Server, the PIVOT operator simplifies this:

SELECT year, [A] AS product_A, [B] AS product_B
FROM sales
PIVOT (SUM(revenue) FOR product IN ([A], [B])) AS pivoted;

Here, PIVOT automatically handles aggregation and column creation. However, you must explicitly list the values (e.g., [A], [B]) to pivot into columns. This approach is cleaner but less flexible if categories change over time. PostgreSQL’s crosstab function offers similar functionality but requires enabling the tablefunc extension and using a specific syntax.

When pivoting, consider dynamic SQL if categories aren’t known in advance. For instance, generating column names programmatically using stored procedures or application code. However, dynamic pivoting can introduce complexity and security risks (e.g., SQL injection). Always validate inputs and test performance, as pivoting large datasets can be resource-intensive. Use database-specific tools where available, but stick to conditional aggregation for cross-database compatibility.