I know that there are a few questions out there on Dynamic SQL and Pivoting, but I'm unable to figure out the issue that I came across since I'm not familiar with Dynamic SQL/Pivoting.
So, I have the data as follows that contains the weekly sales.
The numbers you see as columns refer the weeks of a year, and the columns are populated once the data is updated, and the week numbers don't go beyond 53, which means we know how many columns are going to be returned.
I can use static unpivot to handle this one, but the reason why I'm using Dynamic SQL to pull this off is to work with something new that I'm not familiar with, and trying to learn more about it, and the other reason is to get some knowledge that I can use dynamic sql on our monthly sales data, since I don't know how many columns are going to be returned and how can I put the unknown number of columns returned of the dynamic sql into a local table.
I've made some research and tried to unpivot the data to normalize with the following query, and it seems to be working.
-- create the dynamic date columns string declare @cols AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(column_name) FROM ( -- get columns as a list select TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'WEEKLY_SALES' and ORDINAL_POSITION not in (1,2,3,4,5) ) cols ORDER BY ORDINAL_POSITION FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'' ) -- Unpivot using dynamic sql declare @sqlStr nvarchar(max) set @sqlStr = N' select AREAS,COMPANY,PRODUCT,MARKET,SUBTERRITORIES, Col as WEEK, UNITS from( select AREAS,COMPANY,PRODUCT,MARKET,SUBTERRITORIES, ' + @cols + ' from WEEKLY_SALES ) as cp unpivot ( UNITS for Col in (' + @cols + ') ) as up' exec sp_executesql @sqlStr
Now, I'm looking for a way to put the result of the above query into a local table. Could you please help me in doing this? The thing I'm going to do is, to truncate the table every week and insert the data with the updated weekly sales.
I've created a table where I will put the results into, and performed the following query, but it returns the error messages mentioned below.
DECLARE @queryUpdate AS NVARCHAR(MAX), @queryInsert AS NVARCHAR(MAX); SET @queryInsert = 'INSERT INTO dbo.WEEKLY_SALES_ (AREAS, COMPANY, PRODUCT, MARKET, SUBTERRITORIES, ' + @cols + ') SELECT src.AREAS, src.COMPANY, src.PRODUCT, src.MARKET, src.SUBTERRITORIES ' + REPLACE(@cols, '[', 'src.[') + ' FROM (' + @sqlStr + ') as src LEFT JOIN dbo.WEEKLY_SALES_ as dest ON src.COMPANY = dest.COMPANY AND src.AREAS = dest.AREAS AND src.PRODUCT = dest.PRODUCT AND src.MARKET = dest.MARKET AND src.SUBTERRITORIES = dest.SUBTERRITORIES WHERE dest.COMPANY IS NULL;'; PRINT (@queryInsert); EXECUTE (@queryInsert);
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'as'.
Is there anything else am I missing?