将结果表转换为数据透视表

本文介绍了将结果表转换为数据透视表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我试图在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_WeekFROM [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_saleFROM [DATABASE_SPAIN_EURO.CSV] as t3WHERE t3.[Sales Value with Innovation] is NOT NULLand t3.[Sales Value with Innovation] <>0and t3.[Level]=''Item'' GROUP BY t3.[Level], t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description]) as t2WHERE 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 NULLand t.[Sales Value with Innovation] <>0and t2.first_week_on_sale >=1and t2.weeks_on_sale <=52UNION ALLSELECT 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_WeekFROM [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_saleFROM [DATABASE_FRANCE_EURO.CSV] as t3WHERE t3.[Sales Value with Innovation] is NOT NULLand t3.[Sales Value with Innovation] <>0and t3.[Level]=''Item'' GROUP BY t3.[Level], t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description]) as t2WHEREt.[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 NULLand t.[Sales Value with Innovation] <>0and t2.first_week_on_sale >=1and 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，所以我认为它不可行使用存储过程来创建一个指定列数的表。 有人可以帮我解决一下这个问题吗？ b $ b Best Rgds。What i need is:1] to create the resultant tbl_ALLCOMBINED table2] 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.推荐答案 这篇关于将结果表转换为数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！