How Can I Easily Transpose Columns and Rows in SQL?

Easily implement SQL column and row transposition

In SQL, it is often necessary to exchange rows and columns. While PIVOT seems like a suitable tool, there are situations where it appears to be overly complex. Fortunately, there are some easier alternatives to achieve this conversion.

Use UNION ALL, aggregate functions and CASE statements

If PIVOT seems daunting, you can use UNION ALL combined with an aggregate function and a CASE statement to replicate its functionality:

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

UNPIVOT and PIVOT using static values

If you know the specific column and color you want to convert, you can hardcode it for static execution:

<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)
  ) unpiv
) src
pivot
(
  sum(value)
  for color in ([Red], [Green], [Blue])
) piv</code>

Use dynamic UNPIVOT and PIVOT to handle unknown values

For cases where the number of columns and colors is unknown, dynamic SQL can be used to generate the necessary 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 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)</code>

No matter which method you choose, the results are the same:

| 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 Can I Easily Transpose Columns and Rows in SQL?. For more information, please follow other related articles on the PHP Chinese website!