Pivoting

PIVOTING in SQL Server
1. Create a table named tbResult as follows
SQL> create table tbResult(
2 RollNo int,
3 Name varchar(30),
4 CourseCode varchar(15),
5 Grade varchar(2));
Table created.
2. Insert data to tbResult as follows
insert into tbResult values(1,’Mosarraf’,’CSE-101′,’A+’);
insert into tbResult values(1,’Mosarraf’,’CSE-102′,’B+’);
insert into tbResult values(1,’Mosarraf’,’CSE-107′,’C+’);
insert into tbResult values(2,’Hossain’,’CSE-101′,’A-’);
insert into tbResult values(2,’ Hossain ‘,’CSE-102′,’B-’);
insert into tbResult values(2,’ Hossain ‘,’CSE-107′,’C-’);
insert into tbResult values(3,’Hossain’,’CSE-101′,’A’);
insert into tbResult values(3,’ Hossain ‘,’CSE-112′,’B’);
3. Here is the table with data
SQL> select * from tbResult;
ROLLNO NAME COURSECODE GRADE
———- —————————— ————— –
1 Mosarraf CSE-101 A+
1 Mosarraf CSE-102 B+
1 Mosarraf CSE-107 C+
2 Hossain CSE-101 A-
2 Hossain CSE-102 B-
2 Hossain CSE-107 C-
3 Habib CSE-101 A
3 Habib CSE-112 B
8 rows selected.
4. Create script for pivoting as follows
–PIVOT script
DECLARE @Cols NVARCHAR(2000)
SET @Cols=”
–collect distinct month we have in table
SELECT @Cols = @Cols + ‘[‘+ CourceCode +’]‘+ ‘, ‘ FROM
(SELECT DISTINCT CourceCode FROM tbResult) AS s order by s.CourceCode

print @Cols

–remove last comma
SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
print @Cols

–our pivot will display Month name though we have Month number in table.
SET @Cols=’SELECT * from

(select Roll,CourceCode ,Grade FROM tbResult) as R
PIVOT (Min(Grade) for CourceCode in (‘+@cols+’)) as G
–execute our dynamic query resides in @COL variable

EXECUTE sp_executeSQL @Cols
GO
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s