Generate column numbers using dynamic SQL and pivot command

hi,

the data is ~101 mil records. excel pivot doesnt accept that huge amount to transform and also out of memory issues. In transformed form, the description column becomes unique and the sales values are spread aggregated across the 52 sale week columns. also data is filtered by kpi and country. so get reduced set. this i can then pull into excel easily.

what i want is: 1] instead of writing all the numbers from 1 to 52, i want SQL to do it dynamically. 2] i want to call this stored procedure from excel, passing it parameters like KPI & Country and return the result set to excel.

no one on this forum knows how to use dynamic sql to generate 1 to 52 numbers in pivot column?

:ermm:

I wouldn't use PIVOT for this. I would use a cross tab. You could this either static or dynamic. Take a look at the links in my signature about cross tabs.

hi Sean,

thanks for replying.

if i execute the above query in an sql editor without putting it into a stored proc, it gives me the result i want!

however, i donot want to type in the 1 to 52 Sale_Week column numbers and therefore want to use dynamic sql and a stored procedure to run this query.

i donot understand why i should be using a cross tab as i am a newbie. kindly elaborate.

do you mean that the above query cannot be put in a stored procedure and the 1 to 52 Sale_Week columns cannot dynamically generated?

Read the articles I suggested. A cross tab is much easier to code and most times is faster than a PIVOT.

NO, I did not say that or even elaborate as such. Of course it can be used in a stored procedure and yes the columns can be generated dynamically.

I will surely look into crosstabs later.

Right now, can you help me with the sp and dynamic generation of columns as right now i am pulling my hair and not getting the syntax correct.

All i want to do is call this sp from excel passing it 2 parameters viz., @country & @kpi so that it returns me the pivoted data into excel. Since the pivoted Sale_Week field has 1 to 52 (1 year) or 1 to 156 (3 year) numbers, i want to generate the column numbers accordingly.

USE [ABC]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[ItemWeekwise_Country_KPI]

-- Add the parameters for the stored procedure here

@Country nvarchar(MAX) = '',

@kpi nvarchar(MAX) = ''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX)

-- Insert statements for procedure here

-- HERE I WANT TO USE DYNAMIC SQL TO GENERATE THE COLUMN NUMBERS

SET @SQL = 'SELECT [sCHAR],[sCOUNTRY],[Category],[Manufacturer],[Brand],[Description],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52]

FROM

(

SELECT [sCHAR]

,[sCOUNTRY]

,[Category]

,[Manufacturer]

,[Brand]

,[Description]

,['+@KPI+']

,[Sale_Week]

FROM [dbo].[ITEMWEEKWISE]

WHERE sCOUNTRY=''' + @COUNTRY + '''

) AS T

PIVOT

(

SUM([' + @kpi + '])

-- HERE I WANT TO USE DYNAMIC SQL TO GENERATE THE COLUMN NUMBERS

for [Sale_Week] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52])

) AS PT

ORDER BY PT.SCHAR, PT.sCOUNTRY, PT.Category,PT.Manufacturer, PT.Brand, PT.Description

'

EXEC @SQL;

SET NOCOUNT OFF;

END

In other words you don't care if it is done right as long as it is done right now. But you can't figure it out and need somebody to help you.

What do you mean by you want to use dynamic sql to generate the numbers? What would it be based on? In order to really offer much help we need to know what the structure of these tables are like and some sample data that demonstrates the issue. Please take a few minutes and read the first article in my signature for best practices when posting questions.

on your other "issue",,,have you determined how to pass the parameters from excel to the sp?

It's not that we can't, it just seems useless if the weeks will always be 52.

This is an example on how to do it.

Note that I changed the code to prevent SQL Injection.

To get some references on how I created the weeks string check the following articles:

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

Creating a comma-separated list[/url]

ALTER PROCEDURE [dbo].[ItemWeekwise_Country_KPI]

-- Add the parameters for the stored procedure here

@Country nvarchar(MAX) = '',

@kpi nvarchar(MAX) = 'Something'

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX),

@Weeks NVARCHAR(255)

SET @Weeks = STUFF((SELECT TOP 52 ',[' + CAST( ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(2)) + ']'

FROM sys.all_columns

FOR XML PATH('')), 1, 1, '')

-- Insert statements for procedure here

-- HERE I WANT TO USE DYNAMIC SQL TO GENERATE THE COLUMN NUMBERS

SET @SQL = ' SELECT [sCHAR],[sCOUNTRY],[Category],[Manufacturer],[Brand],[Description],' + @Weeks + '

FROM

(

SELECT [sCHAR]

,[sCOUNTRY]

,[Category]

,[Manufacturer]

,[Brand]

,[Description]

,'+QUOTENAME(@KPI)+'

,[Sale_Week]

FROM [dbo].[ITEMWEEKWISE]

WHERE sCOUNTRY= @COUNTRY

) AS T

PIVOT

(

SUM('+QUOTENAME(@KPI)+')

for [Sale_Week] IN (' + @Weeks + ')

) AS PT

ORDER BY PT.SCHAR, PT.sCOUNTRY, PT.Category,PT.Manufacturer, PT.Brand, PT.Description

'

PRINT @SQL;

EXEC sp_executesql @SQL, N'@COUNTRY NVARCHAR(MAX)', @COUNTRY

SET NOCOUNT OFF;

END

GO

in my opinion I think what you are trying to do from excel is potentially flawed....you say that you have over 100M rows to analyse.

if you can create the necessary VBA in excel to pass parameters to the sp...what are you going to do when the business requirement changes?

say for example...

sales by brand...week on week comparison?

sales by category this year to date v last year to date?

yadda, yadda -----------

have you considered using a "cube" (SSAS) and linking excel to that...or PowerPivot addin to excel?

this will give end users far more flexibility in analysing the data "ad hoc"

it is a learning curve that you will have to take though.....

plenty of training articles around should you care to pursue

Luis,

Thanks a bunch! 🙂

this works perfectly - though now i will need to sit and understand your code.

Also, do i need to add an output variable and OUTPUT keyword at end of procedure name to get this resultant dataset into an output variable? I want to get the result into excel.

Sean/J Livingston,

please refer to this post:

http://www.sqlservercentral.com/Forums/Topic1586986-3412-1.aspx

I have multiple huge CSV's containing 3 years data (156 weeks) for each country & char (global or euro) which i receive in a network folder. I want to monthly schedule a scan of the folder (still dont know how to do that) and pull each CSV data into a database and club them using UNION ALL.

Now i fit this 3 years Innovation data into a 52 week window (as any innovation SKU runs only for <=52 weeks only in the 3 years) and as such i number the Description (SKU) records from 1 to 52 accordingly (Sale_Week column as shown in sql code). Once i have the resultant huge table i need to PIVOT this table so that the Description (SKU) become unique and their Sale_Week columns (1 to 52) columns show their summation across 52 weeks.

This SQL if i have in a stored procedure form, then i can call this SP from excel by passing parameters to this SQL which will again filter result by Country & KPI - the resultant data which i then return to excel.

Being a novice, explaining to the best of my ability. if something not clear, please let me know so that i can try elaborating more on it.

sCHARsCOUNTRYCategoryManufacturerBrandDescription12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152

EUROFRANCEFRESHENERSALL OTHERALL OTHER21-HLS-ESSNTLLS INSTNTN ATOMSR LIQUID STANDRD 200ML X112.6394996691.03579712278.17080688454.31930542523.47680664693.673522951308.53576661280.209960941448.512695311679.904052731810.113647461737.33715822104.123046882160.613525391837.20739746NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

EUROFRANCEFRESHENERSALL OTHERALL OTHER41-HLS-INSTNTN LIQUID BOUTLL STANDRD 200ML X1 +1-GRTT114.97909546470.36691284813.976318361010.07891846837.08599854607.00982666876.92889404787.8493042789.20666504548.54907227586.14941406647.109375408.32849121340.87878418353.86880493385.09820557381.44818115302.94909668336.93850708423.37719727521.87799072309.3573913614.9888000575.5780944815.1991996860.79970169NULLNULL29.9790992714.9888000514.9888000593.33879852429.86001587286.8999939299.8999939329.20001221373.20001221438.94998169638.79998779423.36999512872.04998779735.15002441606.79998779313.5397.19998169558.79998779400.8999939179.6000061180.3999939260.1000061399NULL

EUROFRANCEFRESHENERSALL OTHERALL OTHERMDTRRNNS LNT SANS-INDCTN LIQUID BOITE STANDRD 100ML X1NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL435.59109497444.58538818465.69812012476.39260864654.26800537594.88122559414.19760132598.60351563694.93469238735.24212646520.84991455609.92266846589.11157227538.91192627616.72027588466.4921875640.65338135598.34381104477.60580444434.3059082356.01269531322.18280029498.62658691438.59710693487.73001099409.70300293606.63018799417.2862854487.92520142610.25042725453.22738647585.84667969547.73376465566.60546875553.6262207731.86242676582.5413208487.49801636

[p][/p]

I still don't understand why you need the columns to be dynamic. The number of columns doesn't change. Code it once and be done with it. The dynamic solution saves you some hassle to write it but will run slower.