UNPIVOT Clause

Description

The UNPIVOT clause transforms multiple columns into multiple rows used in SELECT clause. The UNPIVOT clause can be specified after the table name or subquery.

Syntax

  1. UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ] (
  2.     { single_value_column_unpivot | multi_value_column_unpivot }
  3. ) [[AS] alias]
  5. single_value_column_unpivot:
  6.     values_column
  7.     FOR name_column
  8.     IN (unpivot_column [[AS] alias] [, ...])
  10. multi_value_column_unpivot:
  11.     (values_column [, ...])
  12.     FOR name_column
  13.     IN ((unpivot_column [, ...]) [[AS] alias] [, ...])

Parameters

  • unpivot_column

    Contains columns in the FROM clause, which specifies the columns we want to unpivot.

  • name_column

    The name for the column that holds the names of the unpivoted columns.

  • values_column

    The name for the column that holds the values of the unpivoted columns.

Examples

  1. CREATE TABLE sales_quarterly (year INT, q1 INT, q2 INT, q3 INT, q4 INT);
  2. INSERT INTO sales_quarterly VALUES
  3.     (2020, null, 1000, 2000, 2500),
  4.     (2021, 2250, 3200, 4200, 5900),
  5.     (2022, 4200, 3100, null, null);
  7. -- column names are used as unpivot columns
  8. SELECT * FROM sales_quarterly
  9.     UNPIVOT (
  10.         sales FOR quarter IN (q1, q2, q3, q4)
  11.     );
  12. +------+---------+-------+
  13. | year | quarter | sales |
  14. +------+---------+-------+
  15. | 2020 | q2      | 1000  |
  16. | 2020 | q3      | 2000  |
  17. | 2020 | q4      | 2500  |
  18. | 2021 | q1      | 2250  |
  19. | 2021 | q2      | 3200  |
  20. | 2021 | q3      | 4200  |
  21. | 2021 | q4      | 5900  |
  22. | 2022 | q1      | 4200  |
  23. | 2022 | q2      | 3100  |
  24. +------+---------+-------+
  26. -- NULL values are excluded by default, they can be included
  27. -- unpivot columns can be alias
  28. -- unpivot result can be referenced via its alias
  29. SELECT up.* FROM sales_quarterly
  30.     UNPIVOT INCLUDE NULLS (
  31.         sales FOR quarter IN (q1 AS Q1, q2 AS Q2, q3 AS Q3, q4 AS Q4)
  32.     ) AS up;
  33. +------+---------+-------+
  34. | year | quarter | sales |
  35. +------+---------+-------+
  36. | 2020 | Q1      | NULL  |
  37. | 2020 | Q2      | 1000  |
  38. | 2020 | Q3      | 2000  |
  39. | 2020 | Q4      | 2500  |
  40. | 2021 | Q1      | 2250  |
  41. | 2021 | Q2      | 3200  |
  42. | 2021 | Q3      | 4200  |
  43. | 2021 | Q4      | 5900  |
  44. | 2022 | Q1      | 4200  |
  45. | 2022 | Q2      | 3100  |
  46. | 2022 | Q3      | NULL  |
  47. | 2022 | Q4      | NULL  |
  48. +------+---------+-------+
  50. -- multiple value columns can be unpivoted per row
  51. SELECT * FROM sales_quarterly
  52.     UNPIVOT EXCLUDE NULLS (
  53.         (first_quarter, second_quarter)
  54.         FOR half_of_the_year IN (
  55.             (q1, q2) AS H1,
  56.             (q3, q4) AS H2
  57.         )
  58.     );
  59. +------+------------------+---------------+----------------+
  60. |  id  | half_of_the_year | first_quarter | second_quarter |
  61. +------+------------------+---------------+----------------+
  62. | 2020 | H1               | NULL          | 1000           |
  63. | 2020 | H2               | 2000          | 2500           |
  64. | 2021 | H1               | 2250          | 3200           |
  65. | 2021 | H2               | 4200          | 5900           |
  66. | 2022 | H1               | 4200          | 3100           |
  67. +------+------------------+---------------+----------------+