将结果表转换为数据透视表 [英] Transform resultant table into pivot form

问题描述

我试图在SQL SERVER 2014中运行多个大型CSV文件中的UNION ALL查询 - 其结果我想进入一个SQL Server中的表。以下是在MSAccess中有效但在SQL Server 2014上无效的查询：

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
')

我需要的是：

1]创建结果tbl_ALLCOMBINED表



2]使用PIVOT或CROSS转换此表带有以下转换的APPLY命令如下所示：

PAGEFIELD：设置为Level ='It em'

COLUMNFIELD：Sale_Week（列显示1到52个数字）

ROWFIELD：sCOUNTRY，sCHAR，CATEGORY，MANUFACTURER，BRAND，DESCRIPTION，EAN（按此顺序）

DATAFIELD：'具有创新的销售价值'



3]转换后的表格可以显示列字段> 255列，即如果我想显示数据域中的所有KPI值？



PS：CSV包含相同数量的列和数据类型但列数> 100，所以我认为它不可行使用存储过程来创建一个指定列数的表。



有人可以帮我解决一下这个问题吗？

< br $> b $ b

Best Rgds。

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.

推荐答案

这篇关于将结果表转换为数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！