?
Solved

how to make month name dynamic on pivot table

Posted on 2014-04-03
2
Medium Priority
?
736 Views
Last Modified: 2014-04-08
Hello I have a stored procedure that at the end of it, produces a pivot output by month_quantity

The 12 months are static and I would like to learn if it is possible to, using the data, and code I have, to make the column names dynamic instead that always returning twelve.

I will use this code to later feed a SSRS report, that is if the column month names can be created dynamically.

Any information and/ or examples would be really appreciated.


Thank you very much.

p.s.

I attached some data and the code that produces the prepivot table and the output pivoted data set
pre-pivot-and-pivoted-output.txt
pivot-data-sample.xlsx
0
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39976683
To make the pivot column list dynamic you will need to use a dynamic SQL. In SQL Server you can assign your SQL statement into a nvarchar variable and then execute it using EXEC statement. Also, I recommend to stick with [Jan], [Feb], [Mar], etc instead of [Jan] AS [Jan_Qty] , [Feb] AS [Feb_Qty], etc. This way it will be easier to work with. Try this:
DECLARE @sql nvarchar(MAX)
DECLARE @cols nvarchar(200)
SET @cols = '[Apr], [May], [Jun], [Jul]'
SET @sql = 'SELECT  [TypeGroup], [Type], [Location] , ' + @cols +
'FROM  (SELECT [TypeGroup] ,[Type] ,[FiscalMonthName] ,[Location] ,[RemainingDailyQuantity] FROM  @Prepivot ) ' + 
'AS SourceTable PIVOT (SUM([RemainingDailyQuantity]) FOR [FiscalMonthName] IN (' +
@cols + ') ) AS PivotTable;'
EXEC @sql

SET @sql = 'SELECT  [TypeGroup] ,[Type] ,[Location] ,' + @cols + ', ISNULL(' +
REPLACE(@cols, ',', ', 0) + ISNULL(') + ', 0) AS [TotalQty] FROM  @OutTable;'
EXEC @sql

Open in new window

0
 

Author Closing Comment

by:metropia
ID: 39986598
great solution. thank you very much!
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question