如何在SQL中高效地转置列和行？

SQL行列转换的简易方法

虽然SQL的PIVOT函数看似适合进行行列转换，但其复杂性可能会令人却步。如果您希望以更简便的方式实现此目标，请考虑以下替代方法：

使用UNION ALL、聚合函数和CASE语句

此方法使用UNION ALL将数据展开，然后使用聚合函数和CASE语句进行透视：

<code class="language-sql">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</code>

静态解构与透视

如果您知道要转换的值，请使用硬编码值进行解构和透视：

<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)
  ) AS unpiv
) AS src
PIVOT
(
  SUM(value)
  FOR color IN ([Red], [Green], [Blue])
) AS piv</code>

动态透视

对于未知数量的列和颜色，请使用动态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 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)</code>

所有三种方法都会产生以下结果：

NAME	RED	GREEN	BLUE
Eric	3	5	1
John	5	4	2
Paul	1	8	2
Tim	1	3	9

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