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

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
Advertisements

12 Responses to “Dynamic PIVOT with month number to month name as header in SQL Server 2008”

  1. nanibabu Says:

    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.

  2. nanibabu Says:

    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.

  3. Anonymous Says:

    I get this error/*————————–execute our dynamic query resides in @COL variableEXECUTE sp_executeSQL @ColsGO————————*/Msg 137, Level 15, State 2, Line 2Must declare the scalar variable "@Cols"

  4. Anonymous Says:

    I get this error/*————————–execute our dynamic query resides in @COL variableEXECUTE sp_executeSQL @ColsGO————————*/Msg 137, Level 15, State 2, Line 2Must declare the scalar variable "@Cols"

  5. Ritesh Shah Says:

    you have to execute complete script given below at the same time.–PIVOT scriptDECLARE @Cols NVARCHAR(2000)SET @Cols=''–collect distinct month we have in tableSELECT @Cols=@Cols+ '['+DATENAME(month,convert(varchar,s.[month])+'-1-1900') +']'+ ', ' FROM(SELECT DISTINCT [MONTH] FROM testing ) AS s order by s.[Month]–remove last commaSET @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) upPIVOT (count(amount) for [month] in ('+@cols+')) AS pivo'–execute our dynamic query resides in @COL variableEXECUTE sp_executeSQL @ColsGO

  6. Ritesh Shah Says:

    you have to execute complete script given below at the same time.–PIVOT scriptDECLARE @Cols NVARCHAR(2000)SET @Cols=''–collect distinct month we have in tableSELECT @Cols=@Cols+ '['+DATENAME(month,convert(varchar,s.[month])+'-1-1900') +']'+ ', ' FROM(SELECT DISTINCT [MONTH] FROM testing ) AS s order by s.[Month]–remove last commaSET @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) upPIVOT (count(amount) for [month] in ('+@cols+')) AS pivo'–execute our dynamic query resides in @COL variableEXECUTE sp_executeSQL @ColsGO

  7. Anonymous Says:

    cool , thanks

  8. Anonymous Says:

    cool , thanks

  9. Anonymous Says:

    is possible to change this code for SQL 2000 ?

  10. Anonymous Says:

    is possible to change this code for SQL 2000 ?

  11. Ritesh Shah Says:

    unfortunately, it is not possible directly in 2000

  12. Ritesh Shah Says:

    unfortunately, it is not possible directly in 2000


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

%d bloggers like this: