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

SQL行列轉換的簡易方法

雖然SQL的PIVOT函數看似適合進行行列轉換，但其複雜性可能會令人卻步。如果您希望以更簡單的方式實現此目標，請考慮以下替代方法：

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

此方法使用UNION ALL將資料展開，然後使用聚合函數和CASE語句進行透視：

SELECT name,
  SUM(CASE WHEN color = 'Red' THEN value ELSE 0 END) AS Red,
  SUM(CASE WHEN color = 'Green' THEN value ELSE 0 END) AS Green,
  SUM(CASE WHEN color = 'Blue' THEN value ELSE 0 END) AS Blue
FROM
(
  SELECT color, Paul AS value, 'Paul' AS name
  FROM yourTable
  UNION ALL
  SELECT color, John AS value, 'John' AS name
  FROM yourTable
  UNION ALL
  SELECT color, Tim AS value, 'Tim' AS name
  FROM yourTable
  UNION ALL
  SELECT color, Eric AS value, 'Eric' AS name
  FROM yourTable
) AS src
GROUP BY name

靜態解構與透視

如果您知道要轉換的值，請使用硬編碼值進行解構和透視：

SELECT name, [Red], [Green], [Blue]
FROM
(
  SELECT color, name, value
  FROM yourTable
  UNPIVOT
  (
    value FOR name IN (Paul, John, Tim, Eric)
  ) AS unpiv
) AS src
PIVOT
(
  SUM(value)
  FOR color IN ([Red], [Green], [Blue])
) AS piv

動態透視

對於未知數量的欄位和顏色，請使用動態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 AS 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 + ')
    ) AS unpiv
  ) AS src
  PIVOT
  (
    SUM(value)
    FOR color IN (' + @colsPivot + ')
  ) AS 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中文網其他相關文章！