How to Pivot Data in SQL Server

This article describes a simple way to pivot SQL server table data.

A database user may always need to view data in user-defined format. These reports might involve summarizing the data on the basis of various criteria. PIVOT is useful in doing so.

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.

sql-server-pivot-example

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.

sql pivot table

Source code on Git hub Source Code on Github

Speak your mind
Please login to post your comment!


Blog Search





If you like my content please feel free to buy me coffee. Buy Me A Coffee