SQL查询：如何重新排列输出（Transpose？）

I have a resultset from SQL Server like this:

我有一个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

But I wanted it in the following fashion:

但我想以下列方式:

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

How to do this?

这个怎么做?

Or else, how can I get it in this format:

或者,我怎么能用这种格式得到它:

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

1 个解决方案

#1

What you need to do to get the result is a two-step process of implementing both the UNPIVOT and then the PIVOT functions.

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

The first step is to UNPIVOT the data, this takes your multiple columns M1, M2, etc and turns them into two columns with the value and the column name:

第一步是UNPIVOT数据,这将获取您的多个列M1,M2等,并将它们变为两列,其中包含值和列名称:

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

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

Once you have performed the UNPIVOT then you can apply the PIVOT to the Zone column:

一旦执行了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;

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

Now if you do not have access to the PIVOT and UNPIVOT function, then you can perform the same using a UNION ALL for the UNPIVOT and an aggregate function with a CASE to replicate the PIVOT:

现在,如果您无法访问PIVOT和UNPIVOT函数,则可以使用UNION的UNION ALL和带有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

See SQL Fiddle with demo

请参阅SQL Fiddle with demo

Finally, if you had an unknown number of columns to either unpivot or pivot, then you could use a dynamic version of this:

最后,如果您有未知数量的列要进行unpivot或pivot,那么您可以使用以下动态版本:

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)

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

All versions will produce the same result.

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

Result:

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