SQL Pivot: How To Convert Rows to Columns

Pivoting data in SQL is a technique used to convert rows into columns, making the data more readable or aligning it better with specific reporting needs. This operation is particularly useful when dealing with data where you want to aggregate or summarize information in a more structured format.

Example Scenario

Let's say you have a table called Sales with the following structure:

You want to pivot this table so that the quarters become columns, resulting in the following structure:

Using SQL PIVOT

In SQL Server, you can achieve this using the PIVOT function. Here's how you can write the query:

SELECT 
    Product,
    [Q1],
    [Q2],
    [Q3],
    [Q4]
FROM 
    (SELECT Product, Quarter, Sales
     FROM Sales) AS SourceTable
PIVOT
(
    SUM(Sales)
    FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable;

Explanation:

1. Source Table: The inner query (SELECT Product, Quarter, Sales FROM Sales) selects the raw data that you want to pivot.

2. PIVOT Clause: The PIVOT function is then applied to the SourceTable. The SUM(Sales) aggregates the sales data for each quarter.

3. FOR Clause: FOR Quarter IN ([Q1], [Q2], [Q3], [Q4]) specifies that the Quarter values will become columns. The values [Q1], [Q2], [Q3], [Q4] are the new column names that the data will be pivoted into.

Additional Considerations

• Dynamic Pivots: If the quarters or column names are dynamic and not known beforehand, you would need to use dynamic SQL to build the pivot query at runtime.
• Other SQL Dialects: Not all SQL databases support the PIVOT function directly (e.g., MySQL). In such cases, you might need to use CASE statements or aggregate functions combined with GROUP BY.

Pivoting in MySQL (without PIVOT function)

If you're using MySQL, which doesn't have a built-in PIVOT function, you can achieve the same result using CASE:

SELECT
    Product,
    SUM(CASE WHEN Quarter = 'Q1' THEN Sales ELSE 0 END) AS Q1,
    SUM(CASE WHEN Quarter = 'Q2' THEN Sales ELSE 0 END) AS Q2,
    SUM(CASE WHEN Quarter = 'Q3' THEN Sales ELSE 0 END) AS Q3,
    SUM(CASE WHEN Quarter = 'Q4' THEN Sales ELSE 0 END) AS Q4
FROM
    Sales
GROUP BY
    Product;

This query achieves a similar result by using conditional aggregation. Each CASE statement checks the Quarter and sums the sales for the corresponding product.

Conclusion

Pivoting rows into columns in SQL helps present data more effectively, especially in reporting scenarios. The PIVOT function simplifies this in SQL Server, while in MySQL, you can use conditional aggregation with CASE statements to achieve the same result.