如何轻松转置 SQL 中的列和行？

轻松实现SQL列与行转置

在SQL中，经常需要交换行和列。虽然PIVOT看起来像是一个合适的工具，但在某些情况下它显得过于复杂。幸运的是，有一些更简单的替代方法可以实现这种转换。

使用UNION ALL、聚合函数和CASE语句

如果PIVOT看起来令人望而生畏，可以使用UNION ALL结合聚合函数和CASE语句来复制其功能：

<code class="language-sql">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</code>

使用静态值的UNPIVOT和PIVOT

如果您知道要转换的特定列和颜色，可以对其进行硬编码以进行静态执行：

<code class="language-sql">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</code>

使用动态UNPIVOT和PIVOT处理未知值

对于列和颜色数量未知的情况，可以使用动态SQL生成必要的列表：

<code class="language-sql">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)</code>

无论您选择哪种方法，结果都是相同的：

| NAME | RED | GREEN | BLUE |

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

以上是如何轻松转置 SQL 中的列和行？的详细内容。更多信息请关注PHP中文网其他相关文章！