SQL UNPIVOT: Transforming Flattened Data Back to Rows 🎯

Welcome to our comprehensive guide on SQL UNPIVOT! In this tutorial, we'll help you understand how to transform data from a column-oriented format to a row-oriented format, which is crucial for analyzing and manipulating data in various ways.

What is SQL UNPIVOT? 📝

SQL UNPIVOT is a powerful function that helps you restructure data in a table where the data is organized in columns, but you want to treat each column as a separate row. This is particularly useful when you have data in a pivoted or column-wise format, but need to perform operations on each column individually.

Why use SQL UNPIVOT? 💡

Imagine you have a sales report that shows the sales of different products for each month in separate columns. If you want to perform operations on each product separately, it would be tedious and time-consuming. With SQL UNPIVOT, you can transform the data into a more manageable format where each product has its own row, making it easier to analyze and manipulate.

Basic Syntax 📝

The SQL UNPIVOT function uses the following syntax:

sql

SELECT *
FROM (
  SELECT column1, column2, ..., columnN, value
  FROM your_table
) AS pivoted_data
UNPIVOT
(
  column_name FOR value IN (column1, column2, ..., columnN)
)

In the above syntax, column1, column2, ..., columnN are the columns you want to UNPIVOT, and value is an alias for the columns that will contain the original values after UNPIVOT.

Example 💡

Let's consider a table named sales with the following data:

+----------+----------+----------+---------+
| Product  | Month_Jan | Month_Feb | Month_Mar |
+----------+----------+----------+---------+
| ProductA | 100      | 200      | 300      |
| ProductB | 50       | 100      | 150      |
| ProductC | 75       | 125      | 200      |
+----------+----------+----------+---------+

To UNPIVOT this data, we can use the following SQL query:

sql

SELECT Product, Month, Sales
FROM (
  SELECT Product, 'Jan' AS Month, Month_Jan AS Sales, 'Feb' AS Month, Month_Feb AS Sales, 'Mar' AS Month, Month_Mar AS Sales
  FROM sales
) AS pivoted_data
UNPIVOT
(
  Sales FOR Month IN (Month_Jan, Month_Feb, Month_Mar)
)

The result will be:

+----------+-------+-------+
| Product  | Month | Sales |
+----------+-------+-------+
| ProductA | Jan   | 100   |
| ProductA | Feb   | 200   |
| ProductA | Mar   | 300   |
| ProductB | Jan   | 50    |
| ProductB | Feb   | 100   |
| ProductB | Mar   | 150   |
| ProductC | Jan   | 75    |
| ProductC | Feb   | 125   |
| ProductC | Mar   | 200   |
+----------+-------+-------+

Now that each product has its own row, it's much easier to perform calculations, filter data, or sort the results as needed.

Quiz 💡

In our next lesson, we'll explore advanced examples and best practices for using SQL UNPIVOT in real-world scenarios. Stay tuned! 💡