如何輕鬆轉置 SQL 中的列和行？

輕鬆實作SQL列與行轉置

在SQL中，常常需要交換行和列。雖然PIVOT看起來像是合適的工具，但在某些情況下它顯得過於複雜。幸運的是，有一些更簡單的替代方法可以實現這種轉換。

使用UNION ALL、聚合函數和CASE語句

如果PIVOT看起來令人望而生畏，可以使用UNION ALL結合聚合函數和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

使用靜態值的UNPIVOT和PIVOT

如果您知道要轉換的特定列和顏色，可以對其進行硬編碼以進行靜態執行：

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

使用動態UNPIVOT和PIVOT處理未知值

對於列和顏色數量未知的情況，可以使用動態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)

無論您選擇哪一種方法，結果都是相同的：

| NAME | RED | GREEN | BLUE |

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

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