SQL转置全表

要转换的示例代码:select LEFT(datename(dw,datetime),3) as DateWeek, sum(ACalls) as A, Sum(BCalls) as Bfrom DataTablegroup by LEFT(datename(dw,datetime),3)表结构:Column DataTypeDateTime DatetimeACalls intBCalls int任何帮助将不胜感激.解决方案为了将数据转置为所需的结果，您将需要同时使用 UNPIVOT 和 PIVOT 函数. UNPIVOT函数采用A和B列并将结果转换为行.然后，您将使用PIVOT函数将day值转换为列:select *from( select day, col, value from yourtable unpivot ( value for col in (A, B) ) unpiv) srcpivot( max(value) for day in (Mon, Tue, Wed, Thu, Fri)) piv请参见带有演示的SQL小提琴.如果使用的是SQL Server 2008+，则可以将CROSS APPLY与VALUES结合使用以取消数据透视.您的代码将更改为以下内容:select *from( select day, col, value from yourtable cross apply ( values ('A', A),('B', B) ) c (col, value)) srcpivot( max(value) for day in (Mon, Tue, Wed, Thu, Fri)) piv请参见带有演示的SQL小提琴.编辑#1，将当前查询应用到上述解决方案中，您将使用与此类似的内容:select *from( select LEFT(datename(dw,datetime),3) as DateWeek, col, value from DataTable cross apply ( values ('A', ACalls), ('B', BCalls) ) c (col, value)) srcpivot( sum(value) for dateweek in (Mon, Tue, Wed, Thu, Fri)) pivI need to do the following transpose in MS SQLfrom:Day A B---------Mon 1 2Tue 3 4Wed 5 6Thu 7 8Fri 9 0To the following:Value Mon Tue Wed Thu Fri--------------------------A 1 3 5 7 9B 2 4 6 8 0I understand how to do it with PIVOT when there is only one column (A) but I can not figure out how to do it when there are multiple columns to transpose (A,B,...)Example code to be transposed:select LEFT(datename(dw,datetime),3) as DateWeek, sum(ACalls) as A, Sum(BCalls) as Bfrom DataTablegroup by LEFT(datename(dw,datetime),3)Table Structure:Column DataTypeDateTime DatetimeACalls intBCalls intAny help will be much appreciated. 解决方案 In order to transpose the data into the result that you want, you will need to use both the UNPIVOT and the PIVOT functions.The UNPIVOT function takes the A and B columns and converts the results into rows. Then you will use the PIVOT function to transform the day values into columns:select *from( select day, col, value from yourtable unpivot ( value for col in (A, B) ) unpiv) srcpivot( max(value) for day in (Mon, Tue, Wed, Thu, Fri)) pivSee SQL Fiddle with Demo.If you are using SQL Server 2008+, then you can use CROSS APPLY with VALUES to unpivot the data. You code would be changed to the following:select *from( select day, col, value from yourtable cross apply ( values ('A', A),('B', B) ) c (col, value)) srcpivot( max(value) for day in (Mon, Tue, Wed, Thu, Fri)) pivSee SQL Fiddle with Demo.Edit #1, applying your current query into the above solution you will use something similar to this:select *from( select LEFT(datename(dw,datetime),3) as DateWeek, col, value from DataTable cross apply ( values ('A', ACalls), ('B', BCalls) ) c (col, value)) srcpivot( sum(value) for dateweek in (Mon, Tue, Wed, Thu, Fri)) piv 这篇关于SQL转置全表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！