SQL Server PIVOT tutorial

Pivot can be a little tricky to use. I will explain with the help of example

-- Create Table First
create table student(
id int not null primary key identity,
rno int not null,
subject varchar(30),
score int,
outof int)

-- Populate it with some data
insert into student values(1,'Biology',60,100)
insert into student values(1,'Chemistry',70,100)
insert into student values(1,'Physics',80,100)
insert into student values(1,'Math',90,100)
insert into student values(2,'Biology',65,100)
insert into student values(2,'Math',85,100)
insert into student values(3,'Math',95,100)

-- check the student table
select * from student

Here is the output

╔════╦═════╦═══════════╦═══════╦═══════╗
║ id ║ rno ║  subject  ║ score ║ outof ║
╠════╬═════╬═══════════╬═══════╬═══════╣
║  2 ║   1 ║ Biology   ║    60 ║   100 ║
║  3 ║   1 ║ Chemistry ║    70 ║   100 ║
║  4 ║   1 ║ Physics   ║    80 ║   100 ║
║  5 ║   1 ║ Math      ║    90 ║   100 ║
║  6 ║   2 ║ Biology   ║    65 ║   100 ║
║  7 ║   2 ║ Math      ║    85 ║   100 ║
║  8 ║   3 ║ Math      ║    95 ║   100 ║
╚════╩═════╩═══════════╩═══════╩═══════╝

Now lets pivot this table

select * from
(
select rno,subject, score from student
) as A
PIVOT
(
sum(score) for subject in ([Biology],[Chemistry],[Physics],Math)
) B

Here is the output

╔═════╦═════════╦═══════════╦═════════╦══════╗
║ rno ║ Biology ║ Chemistry ║ Physics ║ Math ║
╠═════╬═════════╬═══════════╬═════════╬══════╣
║   1 ║ 60      ║ 70        ║ 80      ║   90 ║
║   2 ║ 65      ║ NULL      ║ NULL    ║   85 ║
║   3 ║ NULL    ║ NULL      ║ NULL    ║   95 ║
╚═════╩═════════╩═══════════╩═════════╩══════╝

Lets understand how the PIVOT Query works

In the first part you select a query that you want to pivot. In this example, my query is

select rno,subject, score from student

so this is the data that pivot would be applied on. If you omit some fields in select query, pivot data will change accordingly.

In the next part, you use an aggregate function, typically SUM() or COUNT(). In my example it is

sum(score) for subject in ([Biology],[Chemistry],[Physics],Math)

It is pretty clear from the expression itself. The first select query will be pivoted on “score in each subject”. The values in IN part is actually the column data that appears in the subject column. The data is moved into header fields. In this case we have select only Biology, Chemistry, Physica and Math. If there were more subjects in the Subject column, they will not be shown in the PIVOT table, because we are not displaying them.

Dynamic PIVOT Query

There is a way to select the subject values dynamically. The trick is to read the data first and store those values in a string and write a query and plug that string in.

declare @liststr varchar(max)
declare @query  AS NVARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + '['+ cast(Subject as varchar(50)) + '] '   from (select distinct subject from student) A
select @listStr

set @query = 'SELECT rno,' + @liststr + ' from 
             (
                select distinct rno,subject, score from student
            ) x
            pivot 
            (
                sum(score) for subject in ('+@listStr + ')

            ) p '

--select @query /* this this line for test purpose only */
execute(@query);

Note that I am using distinct when pulling subject names otherwise, the same subject will be pulled twice and will result in error.

Advertisements
This entry was posted in SQL Server 2008 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s