如何在 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方法

如果需要转换的列值已知，可以使用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>

动态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>

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