Pivoting and unpivoting data in T-SQL

 Pivoting and unpivoting data in T-SQL can be extremely useful for transforming your data into different shapes, particularly for reporting and analysis. Here's an overview of how to use the PIVOT and UNPIVOT operators in T-SQL.

Pivot

The PIVOT operator transforms rows into columns. This is particularly useful for creating summary tables or cross-tab reports.

Example: Pivoting Data

Let's assume we have a table named Sales with the following data:

Year	Quarter	SalesAmount
2022	Q1	100
2022	Q2	150
2022	Q3	200
2022	Q4	250
2023	Q1	110
2023	Q2	160
2023	Q3	210
2023	Q4	260

We want to pivot this data so that each quarter becomes a column.

SELECT Year, [Q1], [Q2], [Q3], [Q4]
FROM
(
    SELECT Year, Quarter, SalesAmount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable
ORDER BY Year;

Result:

Year	Q1	Q2	Q3	Q4
2022	100	150	200	250
2023	110	160	210	260

Unpivot

The UNPIVOT operator performs the reverse transformation, turning columns into rows. This is useful for normalizing data or preparing it for certain types of analysis.

Example: Unpivoting Data

Suppose we have a table named QuarterlySales with the following data:

Year	Q1	Q2	Q3	Q4
2022	100	150	200	250
2023	110	160	210	260

We want to unpivot this data so that each quarter becomes a row.

SELECT Year, Quarter, SalesAmount
FROM
(
    SELECT Year, [Q1], [Q2], [Q3], [Q4]
    FROM QuarterlySales
) AS SourceTable
UNPIVOT
(
    SalesAmount FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS UnpivotTable
ORDER BY Year, Quarter;

Result:

Year	Quarter	SalesAmount
2022	Q1	100
2022	Q2	150
2022	Q3	200
2022	Q4	250
2023	Q1	110
2023	Q2	160
2023	Q3	210
2023	Q4	260

Explanation

Pivot

• Source Table: The inner query selects the source data.
• PIVOT: The PIVOT clause aggregates the SalesAmount values and spreads them across new columns specified in the FOR Quarter IN ([Q1], [Q2], [Q3], [Q4]) clause.
• SUM(SalesAmount): This is the aggregation function applied to the SalesAmount values.

Unpivot

• Source Table: The inner query selects the source data.
• UNPIVOT: The UNPIVOT clause transforms the column values into rows. The FOR Quarter IN ([Q1], [Q2], [Q3], [Q4]) clause specifies the columns to be unpivoted.
• SalesAmount FOR Quarter: This specifies the new column names for the values (SalesAmount) and the labels (Quarter).

Practical Considerations

• Performance: Pivoting and unpivoting can be resource-intensive, especially on large datasets. Indexing and query optimization are essential.
• Data Integrity: Ensure that the data being pivoted or unpivoted is clean and consistent to avoid unexpected results.
• Dynamic Pivoting: For scenarios where the columns to be pivoted are not known in advance, dynamic SQL can be used to construct the pivot query at runtime.

These techniques are powerful tools in T-SQL for data transformation, making it easier to reshape data for analysis and reporting.