SQL SQL转置全表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15297809/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL transpose full table
提问by Selrac
I need to do the following transpose in MS SQL
我需要在 MS SQL 中执行以下转置
from:
从：
Day A B
---------
Mon 1 2
Tue 3 4
Wed 5 6
Thu 7 8
Fri 9 0
To the following:
对以下内容：
Value Mon Tue Wed Thu Fri
--------------------------
A 1 3 5 7 9
B 2 4 6 8 0
I understand how to do it with PIVOTwhen 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,...)
我知道PIVOT当只有一列 (A) 时该怎么做，但是当有多个列要转置 (A,B,...) 时，我不知道该怎么做
Example code to be transposed:
要转置的示例代码：
select LEFT(datename(dw,datetime),3) as DateWeek,
sum(ACalls) as A,
Sum(BCalls) as B
from DataTable
group by LEFT(datename(dw,datetime),3)
Table Structure:
表结构：
Column DataType
DateTime Datetime
ACalls int
BCalls int
Any help will be much appreciated.
任何帮助都感激不尽。
回答by Taryn
In order to transpose the data into the result that you want, you will need to use both the UNPIVOTand the PIVOTfunctions.
为了将数据转置为您想要的结果，您需要同时使用UNPIVOT和PIVOT函数。
The UNPIVOTfunction takes the Aand Bcolumns and converts the results into rows. Then you will use the PIVOTfunction to transform the dayvalues into columns:
该UNPIVOT函数采用A和B列并将结果转换为行。然后您将使用该PIVOT函数将day值转换为列：
select *
from
(
select day, col, value
from yourtable
unpivot
(
value
for col in (A, B)
) unpiv
) src
pivot
(
max(value)
for day in (Mon, Tue, Wed, Thu, Fri)
) piv
See SQL Fiddle with Demo.
If you are using SQL Server 2008+, then you can use CROSS APPLYwith VALUESto unpivot the data. You code would be changed to the following:
如果您使用的是 SQL Server 2008+，则可以使用CROSS APPLYwithVALUES来取消数据透视。您的代码将更改为以下内容：
select *
from
(
select day, col, value
from yourtable
cross apply
(
values ('A', A),('B', B)
) c (col, value)
) src
pivot
(
max(value)
for day in (Mon, Tue, Wed, Thu, Fri)
) piv
See SQL Fiddle with Demo.
Edit #1, applying your current query into the above solution you will use something similar to this:
编辑 #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)
) src
pivot
(
sum(value)
for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv
