complete transpose a table with dynamic rows and column



  • I have a mysql database with this structur:

    Name 2019 2020 2021 2022 ...
    Name1 124 98 34.5 NULL
    Name2 102 NULL 34 NULL
    Name3 34 56 97 123
    Name4 NULL NULL 34.5 NULL
    ... ...

    Every year comes new lines and a column is added.

    I want to transpose this table to the structur:

    Year Name1 Name2 Name3 Name4 ...
    2019 124 102 34 NULL ...
    2020 98 NULL 56 NULL ...
    2021 34.5 34 97 34.5 ...
    2022 NULL NULL 123 NULL ...
    ... ...

    The number of columns and rows are dynamic.

    There are many threads with a similar problem (partially transpose with pivot). But I found no solution for this problem.

    Thanks for help.



  • Here's the problem you're facing: an SQL query cannot return "dynamic columns." The columns of a query are fixed at the time it is parsed, i.e. before it begins reading any data. The query can't add more columns to its own select-list depending on the data it reads during execution.

    So you have two choices:

    1. Figure out which distinct values you want to become columns, and build an SQL query with those columns. This could be done by running another query first to SELECT DISTINCT Name ... and then using application code to format the pivot query. Some folks use creative solutions with GROUP_CONCAT() to format the query. I'm sure you've seen these solutions.

    2. The other strategy is to forget about pivoting in SQL. Just fetch the data as it is in your database, and then write application code to present it in a pivoted format.

    That's it. Those are your choices.


    Here's a solution:

    select '2019' as `Year`,
      max(case name when 'Name1' then `2019` end) as `Name1`,
      max(case name when 'Name2' then `2019` end) as `Name2`,
      max(case name when 'Name3' then `2019` end) as `Name3`,
      max(case name when 'Name4' then `2019` end) as `Name4`
    from mytable
    union
    select '2020',
      max(case name when 'Name1' then `2020` end),
      max(case name when 'Name2' then `2020` end),
      max(case name when 'Name3' then `2020` end),
      max(case name when 'Name4' then `2020` end)
    from mytable
    union
    select '2021',
      max(case name when 'Name1' then `2021` end),
      max(case name when 'Name2' then `2021` end),
      max(case name when 'Name3' then `2021` end),
      max(case name when 'Name4' then `2021` end)
    from mytable
    union
    select '2022',
      max(case name when 'Name1' then `2022` end),
      max(case name when 'Name2' then `2022` end),
      max(case name when 'Name3' then `2022` end),
      max(case name when 'Name4' then `2022` end)
    from mytable;
    

    Output, tested on MySQL 8.0.29:

    +------+-------+-------+-------+-------+
    | Year | Name1 | Name2 | Name3 | Name4 |
    +------+-------+-------+-------+-------+
    | 2019 |   124 |   102 |    34 |  NULL |
    | 2020 |    98 |  NULL |    56 |  NULL |
    | 2021 |    35 |    34 |    97 |    35 |
    | 2022 |  NULL |  NULL |   123 |  NULL |
    +------+-------+-------+-------+-------+
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2