Dynamic PIVOT with month number to month name as header in SQL Server 2008

Friday, May 8, 2009 |

I have written quite a few articles about PIVOT but this is somehow bit different than all of the previous article, the need is to create dynamic pivot table which shows month name as a header for country and display the count of sales amount. Main thing is to display month name as a header where as we have month number in data, moreover, it should be dynamic PIVOT as # of month is not fixed, may be 1 or 2 or 12 (can’t have more than 12 -;) )
Let us create one dummy table along with data to start our PIVOT journey.


--table for demo
create table testing
(
country varchar(10),
[Month] int,
Amount int
)
go
--dummy data
insert into testing
select 'A-land',6,100 union all
select 'B-Land',5,110  union all
select 'B-Land',7,90  union all
select 'C-Land',6,200  union all
select 'C-Land',2,70  union all
select 'D-Land',8,30
GO
--PIVOT script
DECLARE @Cols NVARCHAR(2000)
SET @Cols=''
--collect distinct month we have in table
SELECT @Cols=@Cols+ '['+DATENAME(month,convert(varchar,s.[month])+'-1-1900') +']'+ ', ' FROM
(SELECT DISTINCT [MONTH] FROM testing ) AS s order by s.[Month]
--remove last comma
SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
--our pivot will display Month name though we have Month number in table.
SET @Cols='SELECT * from (select country,DATENAME(month,convert(varchar,[month])+ ''-1-1900'') as [Month] ,amount FROM testing) up
PIVOT (count(amount) for [month] in ('+@cols+')) AS pivo'
--execute our dynamic query resides in @COL variable
EXECUTE sp_executeSQL @Cols
GO



If you want to look at my other PIVOT example than do look those at:





Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

6 comments:

nanibabu said...

hi, your articles is very nice.iam new to ssis.iam following ur articles .i using pivot transformation but i did not get the correct result,please help me.wer how will i send my query.

Anonymous said...

I get this error

/*------------------------
--execute our dynamic query resides in @COL variable
EXECUTE sp_executeSQL @Cols
GO
------------------------*/
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@Cols"

Ritesh Shah said...

you have to execute complete script given below at the same time.


--PIVOT script
DECLARE @Cols NVARCHAR(2000)
SET @Cols=''
--collect distinct month we have in table
SELECT @Cols=@Cols+ '['+DATENAME(month,convert(varchar,s.[month])+'-1-1900') +']'+ ', ' FROM
(SELECT DISTINCT [MONTH] FROM testing ) AS s order by s.[Month]
--remove last comma
SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
--our pivot will display Month name though we have Month number in table.
SET @Cols='SELECT * from (select country,DATENAME(month,convert(varchar,[month])+ ''-1-1900'') as [Month] ,amount FROM testing) up
PIVOT (count(amount) for [month] in ('+@cols+')) AS pivo'
--execute our dynamic query resides in @COL variable
EXECUTE sp_executeSQL @Cols
GO

Anonymous said...

cool , thanks

Anonymous said...

is possible to change this code for SQL 2000 ?

Ritesh Shah said...

unfortunately, it is not possible directly in 2000