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

Question

如何在SQL中简单地切换带有行的列？有没有简单的命令要转置？

即把这个结果：

        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 在这种情况下似乎太复杂了。

Answer 1

您可以通过多种方法来转换此数据。在您的原始文章中，您说过PIVOT这种情况似乎太复杂了，但是可以同时使用SQL
Server中的UNPIVOT和PIVOT函数来轻松地应用它。

但是，如果您无权访问这些函数，则可以使用UNION ALL来复制UNPIVOT该函数，然后使用以下CASE语句将其复制到聚合函数PIVOT：

创建表：

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

合并所有，聚合和CASE版本：

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

的UNION ALL执行与UNPIVOT通过变换列中的数据Paul, John, Tim, Eric进行分离。然后，将聚合函数sum()与该case语句一起应用以获取每个函数的新列color。

Unpivot和Pivot静态版本：

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

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

动态数据透视图版本：

如果您有未知数量的列（Paul, John, Tim, Eric在您的示例中），然后要转换的颜色数量未知，则可以使用动态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

动态版两种查询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 |