Mastering PIVOT and UNPIVOT Functionality in T-SQL

In the realm of data analysis and reporting, the ability to reshape and transform data is crucial. T-SQL offers powerful features called PIVOT and UNPIVOT, which allow you to convert data between rows and columns. Here, we will delve into the syntax, provide practical examples, and explain the input and output data for each functionality, enabling you to unlock their full potential.

  1. PIVOT:
    The PIVOT function in T-SQL is designed to rotate rows into columns, producing a pivot table that simplifies data analysis and aggregation based on specific attributes.

Syntax:
To use PIVOT, you need to specify the aggregate function, the column to pivot, and the pivot column(s).

SELECT [AggregateFunction](ColumnToAggregate)
FROM [SourceTable]
PIVOT ([AggregateFunction](ColumnToAggregate)
       FOR [PivotColumn] IN ([Value1], [Value2], ..., [ValueN]))
AS [Alias];

Explanation:

  • [AggregateFunction]: The function used to aggregate the pivoted column.
  • ColumnToAggregate: The column whose values will be aggregated.
  • [SourceTable]: The table containing the data to be pivoted.
  • [PivotColumn]: The column(s) used for pivoting.
  • [Value1], [Value2], …, [ValueN]: The distinct values from the [PivotColumn] that will become the new column headers.
  • [Alias]: An optional alias for the resulting pivoted table.

Example:
Let’s consider a table called SalesData with the following structure:

SalesData (ProductID int, Category varchar(50), Year int, Sales decimal(10,2))

Suppose we want to pivot the sales data based on the year, creating separate columns for each year.

SELECT Category, [2019], [2020], [2021]
FROM SalesData
PIVOT (SUM(Sales) FOR Year IN ([2019], [2020], [2021])) AS P;

Input Data:
SalesData table:

ProductID | Category     | Year | Sales
----------|--------------|------|-------
1         | Electronics  | 2019 | 5000.00
2         | Electronics  | 2019 | 3000.00
1         | Electronics  | 2020 | 8000.00
2         | Electronics  | 2020 | 4000.00
1         | Electronics  | 2021 | 10000.00
2         | Electronics  | 2021 | 6000.00

Output Data:
The query above will produce a result set with columns Category, [2019], [2020], and [2021]. Each row represents the sales data for a particular category in the respective years.

Category    | 2019    | 2020    | 2021
------------|---------|---------|---------
Electronics | 8000.00 | 12000.00| 16000.00
  1. UNPIVOT:
    The UNPIVOT function in T-SQL allows you to transform columns into rows, enabling a more comprehensive analysis of your data.

Syntax:
To use UNPIVOT, you need to specify the columns to unpivot and the target columns for the unpivoted data.

SELECT [Column1], [Column2], ..., [ColumnN]
FROM [SourceTable]
UNPIVOT ([UnpivotColumn] FOR [TargetColumn] IN ([Column1], [Column2], ..., [

ColumnN]))
AS [Alias];

Explanation:

  • [Column1], [Column2], …, [ColumnN]: The columns to be included in the result set.
  • [SourceTable]: The table containing the data to be unpivoted.
  • [UnpivotColumn]: The column(s) that will store the values of the unpivoted data.
  • [TargetColumn]: The column that will contain the names of the unpivoted columns.
  • [Alias]: An optional alias for the resulting unpivoted table.

Example:
Consider a table called EmployeeData with the following structure:

EmployeeData (ID int, Name varchar(50), Salary2019 decimal(10,2), Salary2020 decimal(10,2), Salary2021 decimal(10,2))

Suppose we want to unpivot the salary data, creating a result set with columns ID, Name, Year, and Salary.

SELECT ID, Name, [Year], Salary
FROM EmployeeData
UNPIVOT (Salary FOR [Year] IN (Salary2019, Salary2020, Salary2021)) AS U;

Input Data:
EmployeeData table:

ID | Name    | Salary2019 | Salary2020 | Salary2021
---|---------|------------|------------|-----------
1  | John    | 50000.00   | 55000.00   | 60000.00
2  | Jane    | 60000.00   | 65000.00   | 70000.00
3  | Mark    | 70000.00   | 75000.00   | 80000.00

Output Data:
The query above will produce a result set with columns ID, Name, Year, and Salary. Each row represents the salary data for a particular employee in the respective years.

ID | Name | Year  | Salary
---|------|-------|--------
1  | John | 2019  | 50000.00
1  | John | 2020  | 55000.00
1  | John | 2021  | 60000.00
2  | Jane | 2019  | 60000.00
2  | Jane | 2020  | 65000.00
2  | Jane | 2021  | 70000.00
3  | Mark | 2019  | 70000.00
3  | Mark | 2020  | 75000.00
3  | Mark | 2021  | 80000.00

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top