Pivot And Unpivot In SQL Server

Question

What are Pivot And Unpivot In SQL Server

Tuhin Paul · Answer

What is Pivot in SQL Server? Pivot is a SQL Server operator that rotates data from rows to columns. It's used to transform data from a state of rows to columns, making it easier to analyze and report. SELECT , [first pivoted column] AS , [second pivoted column] AS , ... FROM ( ) AS PIVOT ( ( ) FOR IN ([first pivoted column], [second pivoted column], ...) ) AS ; What is Unpivot in SQL Server? Unpivot is the opposite of Pivot. It's a SQL Server operator that rotates data from columns to rows. It's used to transform data from a state of columns to rows, making it easier to analyze and report. SELECT , , FROM ( ) AS UNPIVOT ( FOR IN ([first column], [second column], ...) ) AS ; Scenario : Suppose you have a table Sales that stores quarterly sales data for products: ProductID Quarter SalesAmount 1 Q1 1000 1 Q2 1500 1 Q3 2000 1 Q4 2500 2 Q1 3000 2 Q2 3500 2 Q3 4000 2 Q4 4500 1. Pivot Example You want to transform the data so that each quarter becomes a column, and the sales amounts are aggregated for each product. SELECT ProductID, [Q1], [Q2], [Q3], [Q4] FROM ( SELECT ProductID, Quarter, SalesAmount FROM Sales ) AS SourceTable PIVOT ( SUM(SalesAmount) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4]) ) AS PivotTable; Output : ProductID Q1 Q2 Q3 Q4 1 1000 1500 2000 2500 2 3000 3500 4000 4500

Tuhin Paul · Answer

Part -2 2. Unpivot Example Now, suppose you have a pivoted table PivotedSales : ProductID Q1 Q2 Q3 Q4 1 1000 1500 2000 2500 2 3000 3500 4000 4500 You want to transform it back into the original format. SELECT ProductID, Quarter, SalesAmount FROM ( SELECT ProductID, Q1, Q2, Q3, Q4 FROM PivotedSales ) AS SourceTable UNPIVOT ( SalesAmount FOR Quarter IN (Q1, Q2, Q3, Q4) ) AS UnpivotTable; Output : ProductID Quarter SalesAmount 1 Q1 1000 1 Q2 1500 1 Q3 2000 1 Q4 2500 2 Q1 3000 2 Q2 3500 2 Q3 4000 2 Q4 4500

Muhammad Imran Ansari · Answer

In SQL Server, PIVOT and UNPIVOT are relational operators used to transform data between rows and columns, making it easier to analyze and present data in a more structured format. PIVOT: The PIVOT operator converts row-level data into a columnar format. It is typically used to aggregate data and rotate rows into columns, making it useful for creating summary tables or cross-tab reports. Example SELECT Product, [2021], [2022] FROM ( SELECT Product, Year, SalesAmount FROM Sales ) AS SourceTable PIVOT ( SUM(SalesAmount) FOR Year IN ([2021], [2022]) ) AS PivotTable; UNPIVOT: The UNPIVOT operator does the opposite of PIVOT. It converts columnar data back into rows. This is useful when you need to normalize data or restructure it for further analysis. Example SELECT Product, Year, SalesAmount FROM ( SELECT Product, [2021], [2022] FROM PivotedSales ) AS SourceTable UNPIVOT ( SalesAmount FOR Year IN ([2021], [2022]) ) AS UnpivotTable; Key Points: PIVOT is used to aggregate and rotate rows into columns. UNPIVOT is used to convert columns back into rows. Both operations are useful for reshaping data to meet specific reporting or analysis needs.

Sreenath Kappoor · Answer

PIVOT and UNPIVOT are powerful operations used to transform data and make it more readable, efficient, and manageable. These operations allow us to manipulate tables by switching between rows and columns, which can be crucial for summarizing data, reporting, and data analysis. Understanding how to use PIVOT and UNPIVOT operators effectively can significantly enhance our data manipulation capabilities. Sample table: CREATE TABLE ProductSales ( Product NVARCHAR(50), Year INT, Sales INT ); INSERT INTO ProductSales (Product, Year, Sales) VALUES ('Product1', 2020, 10000), ('Product1', 2021, 15000), ('Product2', 2020, 20000), ('Product2', 2021, 25000); SELECT * FROM ProductSales; The 'PIVOT' operator enables you to rotate your data from a state of rows to a state of columns. This is particularly useful when you want to generate a summary report from your data. To generate a summary report that shows the sales of each product by year, you can use the 'PIVOT' operator as follows: SELECT * FROM ( SELECT Product, Year, Sales FROM ProductSales ) AS SourceTable PIVOT ( SUM(Sales) FOR Year IN ([2020], [2021]) ) AS PivotTable; UNPIVOT allows you to convert your columns into rows. This is particularly useful when you need to normalize your data. You can use the UNPIVOT operator to convert the pivoted data back to the original table format -- Step 1: Pivot the data and store it in a temporary table SELECT * INTO #PivotedTable FROM ( SELECT Product, Year, Sales FROM ProductSales ) AS SourceTable PIVOT ( SUM(Sales) FOR Year IN ([2020], [2021]) ) AS PivotTable; -- Step 2: Unpivot the data back to its original format SELECT Product, Year, Sales FROM #PivotedTable UNPIVOT ( Sales FOR Year IN ([2020], [2021]) ) AS UnpivotedTable; -- Step 3: Drop the temporary table after use DROP TABLE #PivotedTable;

Tharunkumar Magudeeswaran · Answer

Pivot is converting rows into columns. Unpivot is opposite of pivot which converts column into rows.

Pivot And Unpivot In SQL Server

Answers (5)