如何在SQL中有效率地轉置列和行？

SQL 欄位與行轉置：實用指南

SQL 通常需要轉置資料 - 將表格方向從垂直（列）轉換為水平（行）或相反。雖然存在 PIVOT 命令，但它可能很麻煩。 本指南探討了更簡單的替代方案。

方法 1：UNION ALL、聚合和 CASE 語句

此方法使用 UNION ALL 進行逆透視，然後使用聚合函數（此處為 SUM）和 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>

方法2：靜態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>

方法3：變數列的動態透視

在處理動態數量的列和顏色時，動態 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 中進行資料轉置的通用方法，適應各種資料操作需求。

以上是如何在SQL中有效率地轉置列和行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！