Generate column numbers using dynamic SQL & pivot

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generate column numbers using dynamic SQL & pivot

Author

Topic

sifar786
Starting Member

4 Posts

Posted - 2014-07-01 : 16:03:06

I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week. Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,

DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'

I want to grab the resultant pivoted table back into excel. how to do it?


USE [Database_ABC]
GO

DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'

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]
,[Sales Value with Innovation]
,[Sale_Week]
FROM [dbo].[ItemTable]
WHERE sCOUNTRY='UK' 
) AS T 
PIVOT
(
SUM([Sales Value with Innovation])
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
GO

Any help would be most appreciated.

Best Rgds.

sifar786
Starting Member

4 Posts

Posted - 2014-07-01 : 22:39:36

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.

Best Rgds.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources