在 SQL 中转置列和行的简单方法

发布于11月14日

如何在SQL中简单地切换列和行？

ie将此结果转化为:

        Paul  | John  | Tim  |  Eric
Red     1       5       1       3
Green   8       4       3       5
Blue    2       2       9       1

对此:

        Red  | Green | Blue
Paul    1       8       2
John    5       4       2
Tim     1       3       9
Eric    3       5       1

PIVOT对于这个场景来说似乎太复杂了.

推荐答案

有几种方法可以转换这些数据.在你最初的帖子中，你说PIVOT对于这个场景来说似乎太复杂了，但是使用SQL Server中的UNPIVOT and PIVOT个函数可以非常容易地应用它.

但是，如果您无法访问这些函数，则可以使用UNION ALL到UNPIVOT，然后使用CASE语句到PIVOT的聚合函数来复制这些函数:

Create Table:

CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);

INSERT INTO yourTable
    ([color], [Paul], [John], [Tim], [Eric])
VALUES
    ('Red', 1, 5, 1, 3),
    ('Green', 8, 4, 3, 5),
    ('Blue', 2, 2, 9, 1);

Union All, Aggregate and CASE Version:

select name,
  sum(case when color = 'Red' then value else 0 end) Red,
  sum(case when color = 'Green' then value else 0 end) Green,
  sum(case when color = 'Blue' then value else 0 end) Blue
from
(
  select color, Paul value, 'Paul' name
  from yourTable
  union all
  select color, John value, 'John' name
  from yourTable
  union all
  select color, Tim value, 'Tim' name
  from yourTable
  union all
  select color, Eric value, 'Eric' name
  from yourTable
) src
group by name

见SQL Fiddle with Demo

UNION ALL通过将列Paul, John, Tim, Eric转换为单独的行来执行数据的UNPIVOT.然后应用聚合函数sum()和case语句，以获得每个color的新列.

Unpivot and Pivot Static Version:

SQL server中的UNPIVOT和PIVOT函数都使这种转换更加容易.如果您知道要转换的所有值，可以将它们硬编码为静态版本以获得结果:

select name, [Red], [Green], [Blue]
from
(
  select color, name, value
  from yourtable
  unpivot
  (
    value for name in (Paul, John, Tim, Eric)
  ) unpiv
) src
pivot
(
  sum(value)
  for color in ([Red], [Green], [Blue])
) piv

见SQL Fiddle with Demo

带有UNPIVOT的内部查询执行与UNION ALL相同的功能.它获取列列表并将其转换为行，然后PIVOT执行到列的最终转换.

Dynamic Pivot Version:

如果要转换的列数未知(在示例中为Paul, John, Tim, Eric)，然后 colored颜色数未知，则可以使用动态sql生成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('yourtable') and
               C.name <> 'color'
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(color)
                    from yourtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select name, '+@colsPivot+'
      from
      (
        select color, name, value
        from yourtable
        unpivot
        (
          value for name in ('+@colsUnpivot+')
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for color in ('+@colsPivot+')
      ) piv'

exec(@query)

见SQL Fiddle with Demo

动态版本查询yourtable，然后查询sys.columns表，以生成UNPIVOT和PIVOT的项目列表.然后将其添加到要执行的查询字符串中.动态版本的优点是，如果您有一个colors和/或names的变化列表，这将在运行时生成该列表.

这三个查询将产生相同的结果:

| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric |   3 |     5 |    1 |
| John |   5 |     4 |    2 |
| Paul |   1 |     8 |    2 |
|  Tim |   1 |     3 |    9 |