在SQL中转置列和行的简单方法? [英] Simple way to transpose columns and rows in SQL?

问题描述

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

How do I simply switch columns with rows in SQL? Is there any simple command to transpose?

即得到这个结果:

        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 seems too complex for this scenario.

推荐答案

有几种方法可以转换此数据.在您的原始帖子中，您说过PIVOT对于这种情况似乎太复杂了，但是可以同时使用

There are several ways that you can transform this data. In your original post, you stated that PIVOT seems too complex for this scenario, but it can be applied very easily using both the UNPIVOT and PIVOT functions in SQL Server.

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

However, if you do not have access to those functions this can be replicated using UNION ALL to UNPIVOT and then an aggregate function with a CASE statement to 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小提琴

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

The UNION ALL performs the UNPIVOT of the data by transforming the columns Paul, John, Tim, Eric into separate rows. Then you apply the aggregate function sum() with the case statement to get the new columns for each color.

取消枢轴和枢轴静态版本:

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

Both the UNPIVOT and PIVOT functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:

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

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

The inner query with the UNPIVOT performs the same function as the UNION ALL. It takes the list of columns and turns it into rows, the PIVOT then performs the final transformation into columns.

动态枢轴版本:

如果列数未知(在示例中为Paul, John, Tim, Eric)，然后变换的颜色数未知，则可以使用动态sql将列表生成为UNPIVOT，然后为PIVOT:

If you have an unknown number of columns (Paul, John, Tim, Eric in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT and then 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提琴

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

The dynamic version queries both yourtable and then the sys.columns table to generate the list of items to UNPIVOT and PIVOT. This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of colors and/or names this will generate the list at run-time.

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

All three queries will produce the same result:

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

这篇关于在SQL中转置列和行的简单方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！