Create dynamic PIVOT queries in SQL Server.

In this Script we will look how we can dynamically create the pivot Script to handle the unknown values.

USE AdventureWorks2012;
GO
CREATE TABLE dbo.Players
(
 FormatID INT,
 PlayerID INT ,
 Name NVARCHAR(255) NOT NULL, 
 Formats Varchar(50),
 Average Int
);
INSERT dbo.Players VALUES
(1,1, N'MS Dhoni','ODI',55),
(1,2, N'Rohit Sharma','ODI',40),
(1,3, N'Virat kholi','ODI',60),
(1,4, N'ABD','ODI',60),
(2,1, N'MS Dhoni','TEST',45),
(2,2, N'Rohit Sharma','TEST',35),
(2,3, N'Virat kholi','TEST',56),
(2,4, N'ABD','TEST',65),
(3,1, N'MS Dhoni','T-20',57),
(3,2, N'Rohit Sharma','T-20',50),
(3,3, N'Virat kholi','T-20',56),
(3,4, N'ABD','T-20',68)

SELECT * FROM dbo.Players
Result we look like this 
pivot step1
But we want result something like this:

pivotstep2

We have write the hard coded Pivot Query here

SELECT Name, p.[ODI], p.[T-20], p.[TEST]
FROM
(
 select PlayerID,Name,Formats,Average from dbo.Players
) AS j
PIVOT
(
 SUM(Average) FOR Formats IN ([ODI],[T-20],[TEST])
) AS p

If one  new Formats is introduced then , this PIVOT query no longer gives the complete result.Suppose here we added one new Format in table  i.e. IPL
INSERT INTO dbo.Players values (1,1, N’MS Dhoni’,’IPL’,65)
INSERT INTO dbo.Players values (1,2, N’Rohit Sharma’,’IPL’,45)
INSERT INTO dbo.Players values (1,3, N’Virat kholi’,’IPL’,70)
INSERT INTO dbo.Players values (1,4, N’ABD’,’IPL’,70)

Obviously the hard-coded PIVOT query is not going to pick up this new Format:

SELECT Name, p.[ODI], p.[T-20], p.[TEST]
FROM
(
select PlayerID,Name,Formats,Average from dbo.Players
) AS j
PIVOT
(
SUM(Average) FOR Formats IN ([ODI],[T-20],[TEST])
) AS p

Result will same as above

pivotstep2

Now see the dynamic SQL Pivot Script:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Formats)
 FROM ( select distinct Formats from dbo.Players) AS x;
 print @columns
SET @sql = N'
SELECT Name, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
 select PlayerID,Name,Formats,Average from dbo.Players
) AS j
PIVOT
(
 SUM(Average) FOR Formats IN ('
 + STUFF (REPLACE(@columns, ', p.[', ',['), 1, 1, '')
 + ')
) AS p ';
PRINT @sql;
EXEC sp_executesql @sql;

Capturestep3

Thanks 🙂

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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