0

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.

7
  • 1
    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.
    – Akina
    Commented May 31, 2022 at 11:43
  • 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.
    – maphy-psd
    Commented May 31, 2022 at 16:01
  • See the tags I added.
    – Rick James
    Commented May 31, 2022 at 16:44
  • @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.
    – maphy-psd
    Commented Jun 1, 2022 at 15:49
  • @maphy-psd - OK, you really need "transpose". Suggest you pull the data into your app in a 2-dimensional array, then transpose there.
    – Rick James
    Commented Jun 1, 2022 at 16:02

2 Answers 2

0

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 |
+------+-------+-------+-------+-------+
2
  • 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.
    – maphy-psd
    Commented Jun 1, 2022 at 15:52
  • My stored proc 'discovers' the columns: mysql.rjweb.org/doc.php/pivot
    – Rick James
    Commented Jun 1, 2022 at 16:03
0

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.

1
  • 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'.
    – maphy-psd
    Commented Jun 26, 2023 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.