• <tfoot id='7T4X0'></tfoot>
      <bdo id='7T4X0'></bdo><ul id='7T4X0'></ul>
    <legend id='7T4X0'><style id='7T4X0'><dir id='7T4X0'><q id='7T4X0'></q></dir></style></legend>

      <small id='7T4X0'></small><noframes id='7T4X0'>

    1. <i id='7T4X0'><tr id='7T4X0'><dt id='7T4X0'><q id='7T4X0'><span id='7T4X0'><b id='7T4X0'><form id='7T4X0'><ins id='7T4X0'></ins><ul id='7T4X0'></ul><sub id='7T4X0'></sub></form><legend id='7T4X0'></legend><bdo id='7T4X0'><pre id='7T4X0'><center id='7T4X0'></center></pre></bdo></b><th id='7T4X0'></th></span></q></dt></tr></i><div id='7T4X0'><tfoot id='7T4X0'></tfoot><dl id='7T4X0'><fieldset id='7T4X0'></fieldset></dl></div>

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

        时间:2023-07-18
      1. <tfoot id='GNZRP'></tfoot>

        <legend id='GNZRP'><style id='GNZRP'><dir id='GNZRP'><q id='GNZRP'></q></dir></style></legend>
      2. <small id='GNZRP'></small><noframes id='GNZRP'>

            <tbody id='GNZRP'></tbody>
            <bdo id='GNZRP'></bdo><ul id='GNZRP'></ul>

            1. <i id='GNZRP'><tr id='GNZRP'><dt id='GNZRP'><q id='GNZRP'><span id='GNZRP'><b id='GNZRP'><form id='GNZRP'><ins id='GNZRP'></ins><ul id='GNZRP'></ul><sub id='GNZRP'></sub></form><legend id='GNZRP'></legend><bdo id='GNZRP'><pre id='GNZRP'><center id='GNZRP'></center></pre></bdo></b><th id='GNZRP'></th></span></q></dt></tr></i><div id='GNZRP'><tfoot id='GNZRP'></tfoot><dl id='GNZRP'><fieldset id='GNZRP'></fieldset></dl></div>
                  本文介绍了在 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 对于这种情况来说似乎太复杂了,但是使用 UNPIVOTPIVOT 函数服务器.

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

                  Union All、Aggregate 和 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 with Demo

                  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.

                  Unpivot 和 Pivot 静态版本:

                  SQL Server 中的 UNPIVOTPIVOT 函数使这种转换变得更加容易.如果您知道要转换的所有值,则可以将它们硬编码为静态版本以获得结果:

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

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

                  动态版本查询yourtablesys.columns 表以生成UNPIVOTPIVOT 的项目列表.然后将其添加到要执行的查询字符串中.动态版本的优点是,如果您有 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 中转置列和行的简单方法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                      • <bdo id='Q7Jso'></bdo><ul id='Q7Jso'></ul>

                          <i id='Q7Jso'><tr id='Q7Jso'><dt id='Q7Jso'><q id='Q7Jso'><span id='Q7Jso'><b id='Q7Jso'><form id='Q7Jso'><ins id='Q7Jso'></ins><ul id='Q7Jso'></ul><sub id='Q7Jso'></sub></form><legend id='Q7Jso'></legend><bdo id='Q7Jso'><pre id='Q7Jso'><center id='Q7Jso'></center></pre></bdo></b><th id='Q7Jso'></th></span></q></dt></tr></i><div id='Q7Jso'><tfoot id='Q7Jso'></tfoot><dl id='Q7Jso'><fieldset id='Q7Jso'></fieldset></dl></div>
                          <tfoot id='Q7Jso'></tfoot>
                            <tbody id='Q7Jso'></tbody>
                        1. <small id='Q7Jso'></small><noframes id='Q7Jso'>

                            <legend id='Q7Jso'><style id='Q7Jso'><dir id='Q7Jso'><q id='Q7Jso'></q></dir></style></legend>