I'm using MySQL - Rows to Columns and this tutorial http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/#comment-6128 to help me pivot a table and it's working pretty well. Starting with this:
mediaID q_short_name start_time stop_time audio_link
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
The goal is this:
mediaID CVVNumStartT CVVNumStopT ExpDateStart_time ExpDateStop_time Aud
ee 208 210 308 310 k.mp3
ff 124 127 166 169 j.mp3
I got part of the way there with this code:
CREATE VIEW test__extension AS (
SELECT amr_text.*,
CASE WHEN q_short_name = 'CVV Number' THEN amr_text.start_time END AS
CVV_Start_Time,
CASE WHEN q_short_name = 'CVV Number' THEN amr_text.stop_time END AS
CVV_Stop_Time,
CASE WHEN q_short_name = 'Expiration Date' THEN amr_text.start_time END
AS Expiration_Start_Time,
CASE WHEN q_short_name = 'Expiration Date' THEN amr_text.stop_time END
AS Expiration_Stop_Time, FROM amr_text);
CREATE VIEW test_extension_pivot AS (SELECT mediaID,
SUM(CVV_Start_Time) AS CVV_Start_Time,
SUM(CVV_Stop_Time) AS CVV_Stop_Time,
SUM(Expiration_Start_Time) AS Expiration_Start_Time,
SUM(Expiration_Stop_Time) AS Expiration_Stop_Time,
FROM test_extension GROUP BY mediaID);
This creates columns exactly like the goal table. But now the values for everything except the mediaIDs are rendered as NULL. My questions are, why did they get replaced by NULL, and what can I use instead of SUM to render the values of Expiration and CVV Start and Stop Time as they are in the original table?
