In my previous article we have explained about Pivot in SQL with examples. In SQL, pivoting refers to the process of transforming rows into columns or columns into rows. Pivoting queries can be useful when you need to present your data in a different format than what is stored in the database. This article will explain how to pivot queries in SQL with examples.
Example :
Let’s start with an example dataset. Consider a table named “Sales” that contains the following data:
| Date | Product | Sales |
|---|---|---|
| 2022-01-01 | A | 100 |
| 2022-01-02 | A | 150 |
| 2022-01-01 | B | 200 |
| 2022-01-02 | B | 250 |
In this example, we have sales data for two products, A and B, for two different dates.
Pivoting Rows into Columns
To pivot rows into columns, we use the “PIVOT” operator. The “PIVOT” operator takes the values from a specified column and turns them into column headers. Here’s an example:
SELECT *
FROM Sales
PIVOT (
SUM(Sales)
FOR Product IN ([A], [B])
) AS P
In this query, we are pivoting the “Product” column into column headers. We are also calculating the sum of sales for each product using the “SUM” function. The resulting table looks like this:
| Date | A | B |
|---|---|---|
| 2022-01-01 | 100 | 200 |
| 2022-01-02 | 150 | 250 |
Now we have two columns, “A” and “B”, that represent the sales for each product.
Pivoting Columns into Rows
To pivot columns into rows, we use the “UNPIVOT” operator. The “UNPIVOT” operator takes the column headers and turns them into rows. Here’s an example:
SELECT *
FROM (
SELECT Date, A, B
FROM Sales
) AS P
UNPIVOT (
Sales FOR Product IN (A, B)
) AS U
In this query, we are unpivoting the “A” and “B” columns into rows using the “UNPIVOT” operator. We are also selecting the “Date” column from the “Sales” table. The resulting table looks like this:
| Date | Product | Sales |
|---|---|---|
| 2022-01-01 | A | 100 |
| 2022-01-02 | A | 150 |
| 2022-01-01 | B | 200 |
| 2022-01-02 | B | 250 |
Now we have three columns, “Date”, “Product”, and “Sales”, with each row representing a single sale.
Conclusion
Pivot queries in SQL can be a useful tool for transforming your data into a different format. Whether you’re pivoting rows into columns or columns into rows, the “PIVOT” and “UNPIVOT” operators can help you achieve your desired results.