How to Pivot Data in SQL Server


This article describes 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 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 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 subject 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 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 subject column into columns namely Physics, chemistry and Maths, and data can be seen 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

Download Source code

Speak your mind :
Leave a comment for this article on dotnetbloogers.com