MariaDB query row to column with unique filter

Question

I am trying to convert some rows to columns in a single table and can't get it work. :(

Here is my simple MariaDB table called runningtimes:

So I have tried various methods including pivot or aggregation but I always get errors or not the desired ouput.

What I am looking for is something like:

The column headers should contain the row values and the final reslut should have a filter that only shows the fastest time per runner.

Any help would be appreciated.

Answer 1

Since you didn't post what errors your are getting or the sample of the code that you have tried till now, I can only assume that you must be facing issues with the special characters in your column name.

I can help you with pivoting the table. In your case you need to use (`) back quote with your column names.

So the pivot table code will look like this:

Select 
  entry_id,
  (case when meta_key = "name-1" then meta_value end) as `name-1`,
  max(case when meta_key = "name-2" then meta_value end) as `name-2`,
  max(case when meta_key = "select-1" then meta_value end) as `select-1`,
  max(case when meta_key = "select-2" then meta_value end) as `select-2`,
  min(case when meta_key = "text-1" then meta_value end) as `text-1`
  
from runningtimes
group by entry_id , "name-1", "text-1"

Then in the select statement use where clause to filter data only from London:

Select * from runningtimes_Pivot where `select-2` = "London" 

Code with filter data: https://dbfiddle.uk/cOn9XHyA

Edited answer based on your comment:

Select 
  entry_id,
  `name-1`,
  `name-2`,
  `select-1`,
  `select-2`,
   `text-1`
from
(
Select 
  entry_id,
  (case when meta_key = "name-1" then meta_value end) as `name-1`,
  max(case when meta_key = "name-2" then meta_value end) as `name-2`,
  max(case when meta_key = "select-1" then meta_value end) as `select-1`,
  max(case when meta_key = "select-2" then meta_value end) as `select-2`,
  min(case when meta_key = "text-1" then meta_value end) as `text-1`
from runningtimes
group by entry_id , "name-1", "text-1"
)ref1
group by `name-1`