Transform resultant table into pivot form

See more:

SQL

pivot

Hi,

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:\Downloads\CSV\;HDR=YES', 
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
IIf( t2.first_week_on_sale = 1 and t2.weeks_on_sale <=52, 
((((substring(t.[week],3,4) - 2011) * 52) + right(t.[week],2)) - t2.first_week_on_sale) + (1 + 52 - t2.weeks_on_sale),
((((substring(t.[week],3,4) - 2011) * 52) + right(t.[week],2)) - t2.first_week_on_sale) + 1 ) as Sale_Week
FROM [DATABASE_SPAIN_EURO.CSV] as t, ( SELECT t3.[Level],t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description],
min(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) as first_week_on_sale,
max(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) as last_week_on_sale,
(max(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) -
min(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)))+1 as weeks_on_sale
FROM [DATABASE_SPAIN_EURO.CSV] as t3
WHERE t3.[Sales Value with Innovation] is NOT NULL
and t3.[Sales Value with Innovation] <>0
and t3.[Level]=''Item'' 
GROUP BY t3.[Level], t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description]
) as t2
WHERE 
t.[Level] = t2.[Level]
and t.[Category] = t2.[Category]
and t.[Manufacturer] = t2.[Manufacturer]
and t.[Brand] = t2.[Brand]
and t.[Description] = t2.[Description]
and t.[Sales Value with Innovation] is NOT NULL
and t.[Sales Value with Innovation] <>0
and t2.first_week_on_sale >=1
and t2.weeks_on_sale <=52
UNION ALL
SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
IIf( t2.first_week_on_sale = 1 and t2.weeks_on_sale <=52, 
((((substring(t.[week],3,4) - 2011) * 52) + right(t.[week],2)) - t2.first_week_on_sale) + (1 + 52 - t2.weeks_on_sale),
((((substring(t.[week],3,4) - 2011) * 52) + right(t.[week],2)) - t2.first_week_on_sale) + 1 ) as Sale_Week
FROM [DATABASE_FRANCE_EURO.CSV] as t, ( SELECT t3.[Level],t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description],
min(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) as first_week_on_sale,
max(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) as last_week_on_sale,
(max(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) -
min(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)))+1 as weeks_on_sale
FROM [DATABASE_FRANCE_EURO.CSV] as t3
WHERE t3.[Sales Value with Innovation] is NOT NULL
and t3.[Sales Value with Innovation] <>0
and t3.[Level]=''Item'' 
GROUP BY t3.[Level], t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description]
) as t2
WHERE
t.[Level] = t2.[Level]
and t.[Category] = t2.[Category]
and t.[Manufacturer] = t2.[Manufacturer]
and t.[Brand] = t2.[Brand]
and t.[Description] = t2.[Description]
and t.[Sales Value with Innovation] is NOT NULL
and t.[Sales Value with Innovation] <>0
and t2.first_week_on_sale >=1
and t2.weeks_on_sale <=52
')

What i need is:
1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT or CROSS APPLY command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

can anyone please help me with a solution asap?


Best Rgds.