SQL 查询:如何重新排列输出(转置?

Question

我有一个来自SQL Server的结果集，如下所示：

Zone        M1      M2  M3  M4  M5  M6  M7
NORTH       6233    17743   2   6233    6717    7369    7369
SOUTH       12440   20017   7   8057    9724    13418   13418
WEST        33736   30532   5   2184    2056    1944    1944
EAST        3944    14584   2   728     953     970     970
CENTRAL     6233    3636    2   6233    6717    6825    6825

但我想要以下方式：

M       NORTH   SOUTH   WEST    EAST    CENTRAL
M1      6233    12440   33736   3944    6233
M2      17743   20017   30532   14584   3636
M3      2       7       5       2       2
M4      ....

怎么做？

否则，我如何以这种格式获取它：

Zone    M   Value
EAST    M1  6322
WEST    M1  27387
EAST    M2  2345
....

Answer 1

要获得结果，您需要做的是实现UNPIVOT和PIVOT函数的两步过程。

第一步是UNPIVOT数据，这会将您的多个列M1、M2 等转换为具有值和列名的两列：

select zone, value, col
from data
unpivot
(
  value
  for col in ([M1], [M2], [M3], 
              [M4], [M5], [M6], [M7])
) unpiv;

请参阅带有演示的 SQL 小提琴

执行UNPIVOT后，可以将PIVOT应用于Zone列：

select *
from
(
  select zone, value, col
  from data
  unpivot
  (
    value
    for col in ([M1], [M2], [M3], 
                [M4], [M5], [M6], [M7])
  ) unpiv
) src
pivot
(
  sum(value)
  for zone in ([North], [South], [West], [East], [Central])
) piv;

请参阅带有演示的 SQL 小提琴

现在，如果您无权访问PIVOT和UNPIVOT函数，则可以使用UNPIVOT的UNION ALL和具有复制PIVOT CASE的聚合函数来执行相同的操作：

select col,
  sum(case when zone='North' then value end) North,
  sum(case when zone='South' then value end) South,
  sum(case when zone='West' then value end) West,
  sum(case when zone='East' then value end) East,
  sum(case when zone='Central' then value end) Central
from
(
  select zone, M1 value, 'M1' col
  from data
  union all
  select zone, M2 value, 'M2' col
  from data
  union all
  select zone, M3 value, 'M3' col
  from data
  union all
  select zone, M4 value, 'M4' col
  from data
  union all
  select zone, M5 value, 'M5' col
  from data
  union all
  select zone, M6 value, 'M6' col
  from data
  union all
  select zone, M7 value, 'M7' col
  from data
) un
group by col

请参阅带有演示的 SQL 小提琴

最后，如果您有未知数量的列要取消透视或透视，则可以使用以下内容的动态版本：

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('data') and
               C.name not in ('zone')
         for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT  distinct ',' 
                      + quotename(Zone)
                    from data
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select *
     from
     (
        select zone, value, col
        from data
        unpivot
        (
          value
          for col in ('+ @colsunpivot +')
        ) u
      ) unpiv
      pivot
      (
        max(value)
        for zone in ('+ @colspivot +')
      ) p'
exec(@query)

请参阅带有演示的 SQL 小提琴

所有版本将产生相同的结果。

结果：

| COL | CENTRAL |  EAST | NORTH | SOUTH |  WEST |
-------------------------------------------------
|  M1 |    6233 |  3944 |  6233 | 12440 | 33736 |
|  M2 |    3636 | 14584 | 17743 | 20017 | 30532 |
|  M3 |       2 |     2 |     2 |     7 |     5 |
|  M4 |    6233 |   728 |  6233 |  8057 |  2184 |
|  M5 |    6717 |   953 |  6717 |  9724 |  2056 |
|  M6 |    6825 |   970 |  7369 | 13418 |  1944 |
|  M7 |    6825 |   970 |  7369 | 13418 |  1944 |