21292

how to pivot a table in mysql using model

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?

Answer1:

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

Recommend

  • BrainTree what do I paste in Braintree.create(“YourClientSideEncryptionKey”);
  • I want to eavalute the time needed to perform prediction using a trained model [duplicate]
  • Timer in Ruby performance
  • javascript: I have subtracted two dates to get the difference between them, how do I find the number
  • To display time spent by user on all tasks daily
  • load files into a table using loop in SSIS 2008
  • whereNotBetween giving me error
  • Searchkick advanced search body clause nullifies where clause
  • Countdown Timer
  • How to update CVV of stored credit card on braintree [closed]
  • How to add time constraints in jsprit?
  • Get duration between date time formats
  • memory management and class member variable
  • fabric env.hosts not being identified
  • SQL query for finding difference of two time and sum
  • Google Calendar API - PHP - set event's color
  • Unexpected output from Newton's method
  • Laravel5.1: validate time field (start time should be greater than end time)
  • MYSQL Event Scheduler DELIMITER using PHP
  • cant find the proper event for input field with timepicker
  • Display label stacked barh with values from dataframe
  • PHP SDK for Facebook: Uploading an Image for an Event created using the Graph API
  • When I click submit button in my form, create action is supposed to work but it is rather processed
  • How to get the current date and time after a user select action
  • Create pagination using while loop
  • BigQuery in Dataflow fails to load data from Cloud Storage: JSON object specified for non-record fie
  • Symfony 2 - Extending generated Entity class
  • Partion 20 min slot of time in php from start and end time
  • Retrieved Answered_By response as null
  • update trigger to update records in another table
  • MySQL: select last full 5 minute interval
  • JMeter pre-processed variable as part of report file name
  • several hexbin plots on one PDF page in R
  • Multiprocessing pool example (parallel) is slower than sequential. Trying to understand pool in pyth
  • ValueError: Found arrays with inconsistent numbers of samples
  • C++ Partial template specialization - design simplification
  • Is there a javascript serializer for JSON.Net?
  • Where to put my custom functions in Wordpress?
  • Buffer size for converting unsigned long to string
  • Binding checkboxes to object values in AngularJs