SQL 查询 : How to rearrange the output (Transpose? )

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 Fiddle with Demo

一旦您执行了 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 Fiddle with Demo

现在，如果您无权访问 PIVOT和 UNPIVOT功能，然后您可以使用 UNION ALL 执行相同的操作为 UNPIVOT和一个带有 CASE 的聚合函数复制 PIVOT :

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 Fiddle with demo

最后，如果您有未知数量的列要逆透视或透视，那么您可以使用动态版本:

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 Fiddle with Demo

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

结果:

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