SQL转置全表 [英] SQL transpose full table

问题描述

我需要在MS SQL中进行以下转置

来自:

Day  A  B 
---------
Mon  1  2
Tue  3  4
Wed  5  6
Thu  7  8
Fri  9  0

到以下内容:

Value Mon Tue Wed Thu Fri 
--------------------------
A      1   3   5   7   9
B      2   4   6   8   0

我了解只有一列(A)时如何使用PIVOT进行操作，但是当有多列要转置(A，B，...)时我不知道如何执行该操作

要转换的示例代码:

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)

表结构:

Column DataType
DateTime Datetime
ACalls int
BCalls int

任何帮助将不胜感激.

解决方案

为了将数据转置为所需的结果，您将需要同时使用 PIVOT 函数.

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

请参见带有演示的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)
) src
pivot
(
  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)
) src
pivot
(
  sum(value)
  for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv

I need to do the following transpose in 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 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 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.

解决方案

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

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

这篇关于SQL转置全表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！