complete transpose a table with dynamic rows and column

[jonetta]

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.

[charlenef]

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 |
+------+-------+-------+-------+-------+