PIVOT and UNPIVOT in SQL Server

Question

Hi friends I want to know what is PIVOT and UNPIVOT in SQL Server. Thank you.

Vinitha T · Answer

PIVOT and UNPIVOT operators in SQL is used to change a table-valued expression into another table. It is ideal for reporting and easier to analyse data. Table: PIVOT turns unique values in one column into multiple column and perform aggregations on remaining column value. Here, I have converted by source table to Pivot table UNPIVOT is reverse to Pivot, it turns columns into rows. Here, I have converted Pivot to Unpivot.

Satyapriya Nayak · Answer

Hi Vipendra, You can use the PIVOT and UNPIVOT relational operators to manipulate a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where necessary on any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values. Every PIVOT query involves an aggregation of some type. There are two ways to pivot data: We can convert multiple rows into a single row with repeating groups of columns. We can convert columns on a single row into multiple rows with a generic column and a row type discriminator. Use the following scripts, view plain copy to clipboard print ? CREATE TABLE Sales (State CHAR (2), SalesAmt DECIMAL (18,2)) CREATE TABLE Sales (State CHAR(2), SalesAmt DECIMAL(18,2)) Insert values, view plain copy to clipboard print ? INSERT INTO Sales VALUES ( 'ND' ,10000) INSERT INTO Sales VALUES ( 'SD' ,30000) INSERT INTO Sales VALUES ( 'TN' ,2500.50) INSERT INTO Sales VALUES ( 'OR' ,5500.50) INSERT INTO Sales VALUES ( 'VA' ,6500.50) INSERT INTO Sales VALUES ( 'SD' ,7000) INSERT INTO Sales VALUES ( 'ND' ,8000) SELECT * FROM Sales INSERT INTO Sales VALUES ('ND',10000) INSERT INTO Sales VALUES ('SD',30000) INSERT INTO Sales VALUES ('TN',2500.50) INSERT INTO Sales VALUES ('OR',5500.50) INSERT INTO Sales VALUES ('VA',6500.50) INSERT INTO Sales VALUES ('SD',7000) INSERT INTO Sales VALUES ('ND',8000) SELECT * FROM Sales Here is the result set, Run the following query, view plain copy to clipboard print ? GO SELECT [ND],[SD],[TN],[ OR ],[VA] FROM ( SELECT State,SalesAmt FROM Sales) p PIVOT ( SUM (SalesAmt) FOR State IN ([ND],[SD],[TN],[ OR ],[VA]) ) AS pvt GO SELECT [ND],[SD],[TN],[OR],[VA] FROM (SELECT State,SalesAmt FROM Sales) p PIVOT ( SUM (SalesAmt) FOR State IN ([ND],[SD],[TN],[OR],[VA]) ) AS pvt Here is the result set, UNPIVOT performs almost the reverse operation of PIVOT , by rotating columns into rows. Use the following scripts, view plain copy to clipboard print ? Create the following table , CREATE TABLE StudentMarks( [ Name ] VARCHAR (50), Subject1 VARCHAR (10), Mark1 INT , Subject2 VARCHAR (10), Mark2 INT , Subject3 VARCHAR (10), Mark3 INT ) Create the following table, CREATE TABLE StudentMarks( [Name] VARCHAR(50), Subject1 VARCHAR(10), Mark1 INT, Subject2 VARCHAR(10), Mark2 INT, Subject3 VARCHAR(10), Mark3 INT) Insert values, view plain copy to clipboard print ? INSERT INTO StudentMarks([ Name ],Subject1,Mark1,Subject2,Mark2,Subject3,Mark3) VALUES ( 'AAA' , 'Science' ,98, 'Maths' ,89, 'English' ,76) INSERT INTO StudentMarks([ Name ],Subject1,Mark1,Subject2,Mark2,Subject3,Mark3) VALUES ( 'XXX' , 'Biology' ,78, 'Chemistry' ,85, 'Physics' ,67) INSERT INTO StudentMarks([ Name ],Subject1,Mark1,Subject2,Mark2) VALUES ( 'YYY' , 'Batany' ,60, 'Zoology' ,54) INSERT INTO StudentMarks([ Name ],Subject1,Mark1,Subject2,Mark2) VALUES ( 'ZZZ' , 'Maths' ,67, 'Physics' ,78) SELECT * FROM StudentMarks INSERT INTO StudentMarks([Name],Subject1,Mark1,Subject2,Mark2,Subject3,Mark3) VALUES('AAA','Science',98,'Maths',89,'English',76) INSERT INTO StudentMarks([Name],Subject1,Mark1,Subject2,Mark2,Subject3,Mark3) VALUES('XXX','Biology',78,'Chemistry',85,'Physics',67) INSERT INTO StudentMarks([Name],Subject1,Mark1,Subject2,Mark2) VALUES('YYY','Batany',60,'Zoology',54) INSERT INTO StudentMarks([Name],Subject1,Mark1,Subject2,Mark2) VALUES('ZZZ','Maths',67,'Physics',78) SELECT * FROM StudentMarks Here is the result set, Run the following query, view plain copy to clipboard print ? SELECT [ Name ], SubjectName, case when Subject= 'Subject1' then Mark1 when Subject= 'Subject2' then Mark2 when Subject= 'Subject3' then Mark3 else NULL end as Marks FROM ( SELECT [ Name ], Subject1,Mark1, Subject2,Mark2, Subject3,Mark3 FROM StudentMarks) p UNPIVOT (SubjectName FOR Subject IN (Subject1, Subject2, Subject3) ) AS unpvt; SELECT [Name], SubjectName, case when Subject='Subject1' then Mark1 when Subject='Subject2' then Mark2 when Subject='Subject3' then Mark3 else NULL end as Marks FROM (SELECT [Name], Subject1,Mark1, Subject2,Mark2, Subject3,Mark3 FROM StudentMarks) p UNPIVOT (SubjectName FOR Subject IN (Subject1, Subject2, Subject3) )AS unpvt; Here is the result set, Note that UNPIVOT is not the exact reverse of PIVOT. PIVOT performs an aggregation and hence merges possible multiple rows into a single row in the output. UNPIVOT does not reproduce the original table-valued expression result because rows have been merged. Besides, NULL values in the input of UNPIVOT disappear in the output, whereas there may have been original NULL values in the input before the PIVOT operation. Please refer the below links http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ http://www.sql-programmers.com/using-pivot-and-unpivot.aspx Thanks

Muralidharan Deenathayalan · Answer

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

PIVOT and UNPIVOT in SQL Server

Answers (3)