SQL Server精确表转置 [英] SQL Server Exact Table Transpose

问题描述

如何在SQL中实现精确的转置?

How do you achieve an exact transpose in SQL?

Month | High | Low | Avg
-------------------------
Jan   | 10    | 9   | 9.5
-------------------------
Feb   | 8  |  7   | 7.5
-------------------------
Mar   | 7    | 6   | 6.5
-------------------------

结果

------ Jan | Feb | Mar
--------------------------
High-- 10  | 8   | 7
--------------------------
Low--  9   | 7   | 6
--------------------------
Avg    9.5 | 7.5 | 6.5
--------------------------

推荐答案

为了获得结果，您首先必须 unpivot High，Low和Avg列通过将它们变成行.然后，您将应用 pivot 函数将month值转换为列. (请参阅:MSDN PIVOT/UNPIVOT 文档)

In order to get the result, you will first have to unpivot the High, Low and Avg columns by turning those into rows. Then you will apply the pivot function to convert the month values into columns. (See: MSDN PIVOT/UNPIVOT docs)

由于使用的是SQL Server 2008+，因此可以使用CROSS APPLY和VALUES取消透视.要取消透视的代码是:

Since you are using SQL Server 2008+, you can use CROSS APPLY and VALUES to unpivot. The code to unpivot is:

select t.month, 
  c.col, 
  c.value
from yourtable t
cross apply
(
  values ('High', high), ('Low', Low), ('Avg', Avg)
) c (col, value)

请参见带演示的SQL提琴.这样，结果的格式便可以按月份进行轮换:

See SQL Fiddle with Demo. This gives the result in a format that can then be pivoted by month:

| MONTH |  COL | VALUE |
------------------------
|   Jan | High |    10 |
|   Jan |  Low |     9 |
|   Jan |  Avg |   9.5 |
|   Feb | High |     8 |
|   Feb |  Low |     7 |

一旦数据成行，您将应用数据透视函数，因此代码将为:

Once the data is in rows, you apply the pivot function, so the code will be:

select col, Jan, Feb, Mar
from
(
  select t.month, 
    c.col, 
    c.value
  from yourtable t
  cross apply
  (
    values ('High', high), ('Low', Low), ('Avg', Avg)
  ) c (col, value)
) d
pivot
(
  sum(value)
  for month in (Jan, Feb, Mar)
) piv

请参见带演示的SQL提琴.结果如下:

|  COL | JAN | FEB | MAR |
--------------------------
|  Avg | 9.5 | 7.5 | 6.5 |
| High |  10 |   8 |   7 |
|  Low |   9 |   7 |   6 |

由于您要使用月份名称，所以我怀疑是否需要此版本的动态SQL版本，但是如果您有未知数量的值，那么可以使用动态sql获取结果.

Since you are pivoting month names, I doubt that you need a dynamic SQL version of this but if you had an unknown number of values, then you could use dynamic sql to get the result.

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