SQL Server PIVOT and UNPIVOT

Pivot convert row to columns and unpivot convert  column to rows. It is sometimes essential to represent data in a cross tabulation purpose, mostly used during report generation. Both static and dynamic query can do pivoting. Pivot is a relational operator to rotate data of tabular format. For example purpose ,we are creating a table and inserting data in it.

CREATE TABLE STUDENT

(

       [student_name] VARCHAR(500),

       [class] INT,

       [section] VARCHAR(5),

       [marks] INT

);

INSERT INTO STUDENT

(

        [student_name],

        [class],

        [section],

         [marks]

)

VALUES

    ('Student1 1',5, 'A', 96),

    ('Student1 2',6, 'B', 138),

    ('Student1 3',5, 'C', 37),

    ('Student1 4',7, 'A', 59),

    ('Student1 5',6, 'B', 282),

    ('Student1 6',7, 'C', 212),

    ('Student1 7',5, 'A', 78),

    ('Student1 8',8, 'B', 97),

    ('Student1 9',7, 'C', 60),

    ('Student1 10',6, 'A', 123),

    ('Student1 11',5, 'B', 220),

    ('Student1 12',8, 'A', 87);

Here is the syntax of the Pivot.

SELECT ,  
    [first pivoted column] AS ,  
    [second pivoted column] AS ,  
    ...  
    [ntht pivoted column] AS   
FROM  
    ()   
    AS   
PIVOT  
(  
    ()  
FOR   
[]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS   
; 

 
1) Specify the column to be display
2) Specify the source table, the source of the data from pivot table
3) An aggregation function is required for example AVG,Max,Min,Count,
4) Specify the value to be included in the return.

Example 1

 

select *

from

(

select class, section, marks

from STUDENT

) src

pivot

(

sum(marks)

for section in ([A], [B], [C])

) piv;

Output :

Now , we are going to see haw unpivot can be done.We are inserting all data of pivot to a table called 

"unpivot_student"

select * into unpivot_student

from

(

select class, section, marks

from STUDENT

) src

pivot

(

sum(marks)

for section in ([A], [B], [C])

) piv;

Now , see what are the data in the "unpivot_student"

select * from unpivot_student

Now , we are writing query for unpivot.

Example 2

SELECT class,

marks

FROM (SELECT *

FROM unpivot_student) p

UNPIVOT

(marks

FOR Quarter IN ( [A],[B],[C] ) ) AS unpvt

 Output :