Mysql with two annidate LIKE

Question

I am trying to simplify my problem. I have a mysql table like this:

meta_key       meta_value
rdv_0_date   12082013
rdv_1_date   13082013
rdv_0_time   12h
rdv_1_time 
rdv_place    Paris

I want as my query result two row:

1. 12082013 12h
2. 13082013 

SELECT * from mytable AS mt1
LEFT JOIN mytable AS mt2
WHERE mt1.meta_key LIKE 'rdv_%s_date'
OR mt1.meta_key LIKE rdv_%s_time'

You really need to do it with SQL? It's better to retreive plain values, then combine them with PHP or something. — Heavy, May 20 '13 at 13:29
The term is called **pivoting** and if I'm not wrong, 100000 people ask the same question every day, they just call it things like "rows to columns" etc and here's a great example, found on SO: http://stackoverflow.com/questions/7674786/mysql-pivot-table — N.B., May 20 '13 at 14:13

Aioros · Accepted Answer · 2013-05-20T14:00:50.287

Are you looking for something like this?

SELECT mt1.meta_value, mt2.meta_value
FROM mytable AS mt1
LEFT JOIN mytable AS mt2 ON (SUBSTRING(mt1.meta_key, 5, 1) = SUBSTRING(mt2.meta_key, 5, 1))
WHERE mt1.meta_key LIKE 'rdv\__\_date'
AND mt2.meta_key LIKE 'rdv\__\_time';

I must agree with the previous comment, though, about the convenience of doing this with a not-really-efficient query like this one. If you're using a language on top of MySQL (like PHP) there could be better ways.

Mysql with two annidate LIKE

1 Answers1