how to pivot a table in mysql using model

Question

I need to pivot a table in mysql and am modeling it on PIvoting table around date column in Mysql, 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?

You have curly quotes in your query, you need to use normal single quotes. — Barmar, Jun 08 '18 at 23:19
Thanks Barmar, fixed the quotes and put it in upper case and it ran without error, got Query OK, 0 rows affected. — topplethepat, Jun 08 '18 at 23:31
Creating a view doesn't affect any rows, so `0 rows affected` is the expected output. — Barmar, Jun 08 '18 at 23:33
Are you sure that you don't want to do this in application code? — Strawberry, Jun 09 '18 at 05:39

score 1 · Accepted Answer · answered Jun 10 '18 at 16:28

1

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';

answered Jun 10 '18 at 16:28

Rick James

135,179
13
127
222

This is exactly right. Unfortunately though my table is larger than this so I'm going to have to figure out how to use self-join with a few more rows that need to become columns. – topplethepat Jun 12 '18 at 17:36

how to pivot a table in mysql using model

1 Answers1

Linked