How to Pivot Data in SQL Server

The PIVOT operator is used to transform a set of rows into columns. PIVOT rotates the table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. In addition to that, it also performs aggregations on the remaining column values if required.

Syntax:

    SELECT Column1, Column2, [Column3]
    FROM (SELECT Columns FROM Table_name1) <Alias>
    PIVOT
    ( 
        aggregation_function (column)
        For Pivot_column<column to be pivoted, to become column header>
        IN ( Pivot_Column1, Pivot Column2, ….)
    ) AS <Alias >
    <OrderBy clause can be given which is optional>
        

Example:

To see the working of the PIVOT clause, first create a table named StudentDetails. Write the below given query in SQL Server to create the table

    CREATE TABLE StudentDetails
    (
        StudentName VARCHAR(80),
        Subject VARCHAR(59),
        Marks INT
    )
        

Now insert some records into the table using insert command. We will insert records such as each student has marks for subjects namely Physics, Chemistry, and Maths. Below is the query to insert records into the StudentDetails table.

    INSERT INTO StudentDetails
    VALUES ('Tim Paul',
            'Physics',
            85)
    INSERT INTO StudentDetails
    VALUES ('Tim Paul',
            'Chemistry',
            67)
    INSERT INTO StudentDetails
    VALUES ('Tim Paul',
            'Maths',
            70)
     -----------------------

    INSERT INTO StudentDetails
    VALUES ('Branda Johnson',
            'Physics',
            65)
    INSERT INTO StudentDetails
    VALUES ('Branda Johnson',
            'Chemistry',
            78)
    INSERT INTO StudentDetails
    VALUES ('Branda Johnson',
            'Maths',
            70)
    ----------------------------------

    INSERT INTO StudentDetails
    VALUES ('Kate Berg',
            'Physics',
            70)
    INSERT INTO StudentDetails
    VALUES ('Kate Berg',
            'Chemistry',
            87)
    INSERT INTO StudentDetails
    VALUES ('Kate Berg',
            'Maths',
            90)
     --------------------------------

    INSERT INTO StudentDetails
    VALUES ('John Sean',
            'Physics',
            60)
    INSERT INTO StudentDetails
    VALUES ('John Sean',
            'Chemistry',
            67)
    INSERT INTO StudentDetails
    VALUES ('John Sean',
            'Maths',
            65)
        

Now the StudentDetails table has the following records.

To view the working of PIVOT, run the below given query into SQL Server. The StudentDetails table has data for different students and their marks for each subject. It will convert the data of the subject column into columns namely Physics, Chemistry, and Maths, and data can be seen as student wise for each subject.

    SELECT *
    FROM 
    (
        SELECT
        Studentname, Subject, Marks
        FROM StudentDetails
    ) as s
    PIVOT
    (
         SUM(Marks)
         FOR [Subject] IN (Physics,Chemistry,Maths)
    )
    AS Pivot_alias

        

After running the above query the data can be viewed as below.

The Subject column which previously had values Physics, Chemistry, Maths are now the names of columns. These column contains the marks for each of the student in the table.