UNPIVOT Clause

    • unpivot_column

    • name_column

    • values_column

    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  |
    21. | 2022 | q1      | 4200  |
    22. | 2022 | q2      | 3100  |
    23. +------+---------+-------+
    25. -- NULL values are excluded by default, they can be included
    26. -- unpivot columns can be alias
    27. -- unpivot result can be referenced via its alias
    28. SELECT up.* FROM sales_quarterly
    29.     UNPIVOT INCLUDE NULLS (
    30.         sales FOR quarter IN (q1 AS Q1, q2 AS Q2, q3 AS Q3, q4 AS Q4)
    31.     ) AS up;
    32. +------+---------+-------+
    33. | year | quarter | sales |
    34. +------+---------+-------+
    35. | 2020 | Q1      | NULL  |
    36. | 2020 | Q2      | 1000  |
    37. | 2020 | Q3      | 2000  |
    38. | 2020 | Q4      | 2500  |
    39. | 2021 | Q1      | 2250  |
    40. | 2021 | Q2      | 3200  |
    41. | 2021 | Q3      | 4200  |
    43. | 2022 | Q1      | 4200  |
    44. | 2022 | Q2      | 3100  |
    45. | 2022 | Q3      | NULL  |
    46. | 2022 | Q4      | NULL  |
    47. +------+---------+-------+
    49. SELECT * FROM sales_quarterly
    50.     UNPIVOT EXCLUDE NULLS (
    51.         (first_quarter, second_quarter)
    52.         FOR half_of_the_year IN (
    53.             (q1, q2) AS H1,
    54.             (q3, q4) AS H2
    55.         )
    56.     );
    57. +------+------------------+---------------+----------------+
    58. |  id  | half_of_the_year | first_quarter | second_quarter |
    59. +------+------------------+---------------+----------------+
    60. | 2020 | H1               | NULL          | 1000           |
    61. | 2020 | H2               | 2000          | 2500           |
    62. | 2021 | H1               | 2250          | 3200           |
    63. | 2021 | H2               | 4200          | 5900           |
    65. +------+------------------+---------------+----------------+