Pivot and Unpivot

Pivot

The PIVOT operation is used to rotate rows into columns. It takes a set of values from one column and transforms them into separate columns.

Syntax:

sql
SELECT column1, [column2], ..., [columnN]
FROM table_name
PIVOT (
  aggregate_function (sum | avg | max | min | count) 
  FOR column_name IN ([value1], [value2], ..., [valueN])
)

Example:

Suppose we have a table Sales with the following data:

markdown
+--------+---------+
| Product| Sales   |
+--------+---------+
| A      | 100     |
| B      | 200     |
| C      | 300     |
| A      | 150     |
| B      | 250     |
| C      | 350     |
+--------+---------+

We want to pivot this data to show the sales for each product in separate columns. We can use the following query:

sql
SELECT Product, [A], [B], [C]
FROM Sales
PIVOT (
  SUM(Sales)
  FOR Product IN ([A], [B], [C])
);

Result:

markdown
+--------+----+----+----+
| Product| A | B | C |
+--------+----+----+----+
| A      | 250| 0 | 0 |
| B      | 0 | 450| 0 |
| C      | 0 | 0 | 650|
+--------+----+----+----+

Unpivot

The UNPIVOT operation is used to rotate columns into rows. It takes a set of values from multiple columns and transforms them into separate rows.

Syntax:

sql
SELECT column1, [column2], ..., [columnN]
FROM table_name
UNPIVOT (
  value_column
  FOR column_name IN ([value1], [value2], ..., [valueN])
)

Example:

Suppose we have a table Sales with the following data:

markdown
+--------+----+----+----+
| Product| A | B | C |
+--------+----+----+----+
| A      | 250| 0 | 0 |
| B      | 0 | 450| 0 |
| C      | 0 | 0 | 650|
+--------+----+----+----+

We want to unpivot this data to show each product and its corresponding sales in separate rows. We can use the following query:

sql
SELECT Product, Sales
FROM Sales
UNPIVOT (
  Sales
  FOR Product IN ([A], [B], [C])
);

Result:

markdown
+--------+------+
| Product| Sales |
+--------+------+
| A      | 250   |
| B      | 450   |
| C      | 650   |
+--------+------+

I hope this helps! Let me know if you have any questions or need further clarification.