complete transpose a table with dynamic rows and column

Question

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.

Answer 1

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

Answer 2

Answer suggested by @FortressBuilder:

To transpose the table in MySQL, you can use a combination of dynamic SQL and conditional aggregation. Here's an example query that should achieve the desired result:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Name = ''',
      Name,
      ''' THEN ',
      Year,
      ' END) AS ',
      CONCAT('`', Name, '`')
    )
  ) INTO @sql
FROM your_table;

SET @sql = CONCAT('SELECT Year, ', @sql, ' 
                   FROM your_table
                   GROUP BY Year');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

In this query, we first construct a dynamic SQL statement by selecting the distinct names from the table and generating a conditional aggregation statement for each name and year combination. The result of this construction is stored in the @sql variable.

Next, we execute the dynamic SQL statement, which selects the year and the transposed values for each name using conditional aggregation. The result is grouped by the year.

Please replace your_table with the actual name of your table in the query.

Note that dynamic SQL can introduce security risks if you're not careful with user input, so make sure to properly sanitize the input if it comes from external sources.