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.
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.