How to Efficiently Transpose Columns and Rows in SQL?

Easy way to convert SQL rows and columns

Although SQL's PIVOT function seems suitable for column-column conversion, its complexity may be prohibitive. If you'd like an easier way to achieve this, consider the following alternatives:

Use UNION ALL, aggregate functions and CASE statements

This method uses UNION ALL to expand the data, and then uses aggregate functions and CASE statements to pivot:

<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>

Static deconstruction and perspective

If you know the value you want to convert, use hardcoded values ​​for destructuring and pivoting:

<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>

Dynamic Perspective

For an unknown number of columns and colors, use dynamic SQL to generate deconstructed and pivoted lists:

<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>

All three methods produce the following results:

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

The above is the detailed content of How to Efficiently Transpose Columns and Rows in SQL?. For more information, please follow other related articles on the PHP Chinese website!