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.

• Akina: What a problem? use dynamic SQL... I'd recommend - use 1st dynamic query and normalize the structure (convert source data to name-year-value with dynamic UNION ALL) then use 2nd query and pivot this data to needed output. You may both use intermediate temptable and combine these queries into one. Alternatively you may build single dynamic query with conditional aggregation, but I doubt that it will be more effective.
• maphy-psd: Thanks for the hints. The problem is that I can't manage a query myself. I tried to construct such a query from the other solutions. But unfortunately I did not succeed. I would therefore be grateful for any help.
• Rick James: See the tags I added.
• maphy-psd: @RickJames thanks for the tags. The terms were already known to me, so they do not help me at the moment. I have written that I need help with the query.
• Rick James: @maphy-psd - OK, you really need "transpose". Suggest you pull the data into your app in a 2-dimensional array, then transpose there.

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

• maphy-psd: Thanks for the answer. The query is not supposed to add columns after all. The output table (is actually a view that makes one out of several tables.) is fixed for one year in the query and should be transposed. Every year only one column is added and several rows. Therefore the query should be dynamic. The query itself also becomes a view.
• Rick James: My stored proc 'discovers' the columns: mysql.rjweb.org/doc.php/pivot

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.

• maphy-psd: Thank you for your idea. I got the error: #1054 Unknown column 'Year' in field list. In the original table there is no column 'Year'.