The PIVOT operator transforms rows into columns by aggregating data based on specified column values. It's particularly useful when you need to create cross-tabulations or summaries.

SELECT <non-pivoted column>,
 [first pivoted column] AS <column name>,
 [second pivoted column] AS <column name>,
 ...
 [last pivoted column] AS <column name>
FROM
 (<SELECT query that produces the data>)
 AS <alias for the source query>
PIVOT (
 <aggregation function>(<column being aggregated>)
 FOR [<column that will become the new column headers>]
 IN ( [first pivoted column], [second pivoted column],
 ... [last pivoted column])
) AS <alias for the pivot table>;

Let's consider a scenario where you have a table named SalesData with columns like Category, Product, and SalesAmount. You want to pivot the data to display sales amounts for each product as columns, with categories as rows.

SELECT
 Category,
 [ProductA],
 [ProductB],
 [ProductC]
FROM
 (
 SELECT
 Category,
 Product,
 SalesAmount
 FROM
 SalesData
 ) AS SourceTable PIVOT (
 SUM(SalesAmount)
 FOR Product IN ([ProductA], [ProductB], [ProductC])
 ) AS PivotTable;

The UNPIVOT operator performs the reverse operation of PIVOT. It transforms columns into rows, making it suitable for normalizing data or converting wide tables into longer, narrower ones.

SELECT <non-unpivoted column>,
 <column name for attribute column>,
 <column name for value column>
FROM
 (<SELECT query that produces the data>)
 AS <alias for the source query>
UNPIVOT (
 <value column>
 FOR <attribute column> IN ( [first unpivoted column], [second unpivoted column],
 ... [last unpivoted column])
) AS <alias for the unpivot table>;

Suppose you have a table named ProductSales with columns like Category, ProductA, ProductB, and ProductC, representing sales for each product. You want to unpivot the data to have a single Product column and a SalesAmount column.

SELECT
 Category,
 Product,
 SalesAmount
FROM
 (
 SELECT
 Category,
 ProductA,
 ProductB,
 ProductC
 FROM
 ProductSales
 ) AS SourceTable UNPIVOT (
 SalesAmount
 FOR Product IN (ProductA, ProductB, ProductC)
 ) AS UnpivotTable;

• Data Analysis: Reshape data for trend analysis and comparisons.
• Reporting: Create customized reports with aggregated and transformed data.
• Data Normalization: Convert denormalized data into a normalized format.

The PIVOT and UNPIVOT operators in SQL Server offer powerful capabilities for reshaping data. By understanding their syntax and applications, you can effectively transform data to meet your specific analysis and reporting needs.

```mdx
---
title: "Reshaping Data: PIVOT and UNPIVOT"
description: "Discover how to manipulate data structures in SQL Server by employing the PIVOT and UNPIVOT functionalities. Delve into practical examples and syntax for data transformation processes."
---


# Data Restructuring with PIVOT and UNPIVOT in SQL Server


Within the SQL Server environment, `PIVOT` and `UNPIVOT` stand out as robust mechanisms designed for data reshaping. They empower you to transform rows into columns (known as pivoting) and, conversely, columns into rows (known as unpivoting), thereby enhancing the adaptability of data analysis and reporting procedures.


## Delving into PIVOT


The `PIVOT` functionality remolds rows into columns through the aggregation of data, contingent upon pre-defined column values. It proves especially beneficial when the objective is to generate cross-tabulations or concise summaries.


### PIVOT Syntax Breakdown


```sql
SELECT <non-pivoted column>,
 [first pivoted column] AS <column name>,
 [second pivoted column] AS <column name>,
 ...
 [last pivoted column] AS <column name>
FROM
 (<SELECT query that produces the data>)
 AS <alias for the source query>
PIVOT (
 <aggregation function>(<column being aggregated>)
 FOR [<column that will become the new column headers>]
 IN ( [first pivoted column], [second pivoted column],
 ... [last pivoted column])
) AS <alias for the pivot table>;

Imagine a scenario where a table named SalesData exists, featuring columns such as Category, Product, and SalesAmount. The goal is to pivot this data to showcase sales amounts for each product as distinct columns, while categories are represented as rows.

SELECT
 Category,
 [ProductA],
 [ProductB],
 [ProductC]
FROM
 (
 SELECT
 Category,
 Product,
 SalesAmount
 FROM
 SalesData
 ) AS SourceTable PIVOT (
 SUM(SalesAmount)
 FOR Product IN ([ProductA], [ProductB], [ProductC])
 ) AS PivotTable;

The UNPIVOT functionality executes the inverse operation of PIVOT. It converts columns into rows, rendering it ideal for data normalization or the conversion of expansive tables into more streamlined, elongated formats.

SELECT <non-unpivoted column>,
 <column name for attribute column>,
 <column name for value column>
FROM
 (<SELECT query that produces the data>)
 AS <alias for the source query>
UNPIVOT (
 <value column>
 FOR <attribute column> IN ( [first unpivoted column], [second unpivoted column],
 ... [last unpivoted column])
) AS <alias for the unpivot table>;

Consider a table named ProductSales, which encompasses columns like Category, ProductA, ProductB, and ProductC, representing sales figures for each product. The objective is to unpivot this data, consolidating it into a single Product column and a SalesAmount column.

SELECT
 Category,
 Product,
 SalesAmount
FROM
 (
 SELECT
 Category,
 ProductA,
 ProductB,
 ProductC
 FROM
 ProductSales
 ) AS SourceTable UNPIVOT (
 SalesAmount
 FOR Product IN (ProductA, ProductB, ProductC)
 ) AS UnpivotTable;

• Data Analysis: Reconfigure data for the purposes of trend assessment and comparative studies.
• Reporting: Generate tailor-made reports incorporating aggregated and transformed data elements.
• Data Normalization: Transform data from a denormalized state into a normalized structure.

The PIVOT and UNPIVOT functionalities within SQL Server provide substantial capabilities for data restructuring. By gaining a thorough understanding of their syntax and applications, you can adeptly transform data to align with your specific analytical and reporting demands.

<AppearanceSection></AppearanceSection>