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)

所有三種方法都會產生以下結果：