How to implement converting multiple rows to multiple columns in SQL pivot?

2 years ago
Ava Mitchell
1 minute

In SQL, you can use the CASE WHEN statement in conjunction with aggregate functions to pivot multiple rows of data into multiple columns. The specific steps are as follows:

  1. Organizing by category
  2. IF
  3. Aggregate functions are applied to the transposed columns for aggregation.

Here is an example: consider a table called Orders, which includes columns for OrderID, ProductName, and Quantity.

SELECT OrderID,
       MAX(CASE WHEN ProductName = 'Product A' THEN Quantity END) AS ProductA,
       MAX(CASE WHEN ProductName = 'Product B' THEN Quantity END) AS ProductB,
       MAX(CASE WHEN ProductName = 'Product C' THEN Quantity END) AS ProductC
FROM Orders
GROUP BY OrderID;

The above query will pivot the quantity of each product in every order into their corresponding columns, resulting in a table similar to the one below:

OrderID | ProductA | ProductB | ProductC
--------+----------+----------+---------
1       | 5        | 2        | NULL
2       | 3        | NULL     | 1
3       | NULL     | 4        | 3

In the examples above, the CASE WHEN statement is used to convert the product names into corresponding columns, and the MAX function is used to aggregate the values of each column. If an order does not have a corresponding product, the column will display a NULL value.

bannerAds