Question:

I need to pivot a table in mysql and am modeling it on <a href="https://stackoverflow.com/questions/48129616/pivoting-table-around-date-column-in-mysql" rel="nofollow">PIvoting table around date column in Mysql</a>, which is almost a perfect example of what I need to do. But I'm getting no result when I run it and can't see what I'm doing wrong. The table I have is like this:

mediaID q_short_name start_time stop_time audio_file ee CVV Number 208 210 j.mp3 ee Expiration Date 308 310 j.mp3 ff CVV Number 124 127 k.mp3 ff Expiration Date 166 169 k.mp3

and I want it to be like this:

mediaID CVVstart_T CVVstop_T Exp_start_time Exp_stop_time audio_file ee 208 210 308 310 j.mp3 ff 124 127 166 169 k.mp3

so I tried this as a first step:

Create view my_test_extended as (select my_test.mediaID, case when q_short_name = 'CVV Number' then my_test.start_time end as CVVstart_T from my_test);

This is giving me Query OK, 0 rows affected. How can I adjust the query so I get the result I want?

Let's talk about a "self-join" instead of "pivot".

SELECT c.mediaID, c.start_time AS CVVstart, c.end_time AS CVVstop, e.start_time AS ExpStart, e.stop_time AS ExpStop, c.audio_file FROM my_test AS c JOIN my_test AS e USING(mediaID) WHERE c.q_short_name = 'CVV Number' AND e.q_short_name = 'Expiration Date';