Transposing

We wish to transpose a table so (1) its columns become its rows (2) the column names are converted to a column and (3) the values of a particular column are used as the new column names.

WITH unpivoted AS
         (SELECT *
          FROM table_1
              UNPIVOT (value FOR col IN (b, c, d)))

SELECT *
FROM unpivoted
    PIVOT (ANY_VALUE(value) FOR a IN ('B', 'C', 'D','E'))

Here is how this works:

  • We then use UNPIVOT() to convert columns into rows. unpivoted CTE will have 3 columns "a", "value", and "col". See Pivoting.
  • We pivot the using PIVOT() and convert values in "a" column into columns.
  • The output table is the transposed version of tanle_1. i.e. The first column in table_1 is the column names in the output, and the columns names in table_1 are now the values of the first column in the output.
SQL
I/O