Tutorial: Pivoting and Unpivoting Data in SQL

1. Introduction

Goal

The goal of this tutorial is to guide you through the process of pivoting and unpivoting data in SQL.

Learning Outcomes

After completing this tutorial, you'll be able to transform data between normalized (or 'unpivoted') and pivoted form. You'll know how to manipulate data in SQL to fit your specific needs.

Prerequisites

You should have a basic understanding of SQL (Structured Query Language) and be familiar with the basic SQL operations like SELECT, WHERE, GROUP BY etc.

2. Step-by-Step Guide

Pivoting is the process of rotating data from a state of rows to a state of columns. It's useful when you want to transform your data to get a different perspective.

Unpivoting is the opposite of pivoting. It's the process of rotating data from a state of columns to a state of rows. It can help to normalize your data.

Pivoting

In SQL Server, you can use the PIVOT operator. Here's an example:

SELECT *
FROM (SELECT year, value, country
      FROM table) AS SourceTable
PIVOT
(
AVG(value)
FOR country IN (USA, Canada, Mexico)
) AS PivotTable;

This query would take the value column and create a new column for each unique value in the country column, then fill it with the average value for each year.

Unpivoting

In SQL Server, you can use the UNPIVOT operator. Here's an example:

SELECT year, country, value
FROM   (SELECT year, USA, Canada, Mexico
        FROM   table) p
UNPIVOT
(
value FOR country IN 
      (USA, Canada, Mexico)
)AS unpvt;

This query will unpivot the USA, Canada, Mexico columns from the source table into a country column and a value column.

3. Code Examples

Pivoting Example

-- Let's pivot the data
SELECT *
FROM (SELECT year, value, country
      FROM Sales) AS SourceTable
PIVOT
(
SUM(value)
FOR country IN (USA, Canada, Mexico)
) AS PivotTable;

This code snippet is pivoting the Sales table. It creates a new column for each unique value in the country column and fills it with the sum of the value for each year.

Unpivoting Example

-- Let's unpivot the data
SELECT year, country, value
FROM   (SELECT year, USA, Canada, Mexico
        FROM   Sales) p
UNPIVOT
(
value FOR country IN 
      (USA, Canada, Mexico)
)AS unpvt;

This code snippet unpivots the Sales table. It transforms the USA, Canada, Mexico columns into a country column and a value column.

4. Summary

In this tutorial, you've learned about pivoting and unpivoting data in SQL. Pivoting transforms data from rows to columns, while unpivoting does the opposite. You've seen how to use the PIVOT and UNPIVOT operators in SQL Server.

For more advanced topics on SQL, you can explore concepts like join operations, subqueries, and stored procedures.

5. Practice Exercises

1. Try to pivot a table that has three columns: year, product, and sales. Each product should have its own column after pivoting.

2. Unpivot a table that has four columns: year, productA, productB, and productC. After unpivoting, the table should have three columns: year, product, and sales.

Solutions:

SELECT *
FROM (SELECT year, sales, product
      FROM Sales) AS SourceTable
PIVOT
(
SUM(sales)
FOR product IN (productA, productB, productC)
) AS PivotTable;

SELECT year, product, sales
FROM   (SELECT year, productA, productB, productC
        FROM   Sales) p
UNPIVOT
(
sales FOR product IN 
      (productA, productB, productC)
)AS unpvt;

In the first exercise, we pivoted the product column into multiple columns. In the second, we did the reverse, unpivoting productA, productB, productC columns into a single column.