title: Need to transpose Rows into Columns using WPDataTables to query MySQL DB
body: <p>I'm using a WordPress Plugin that allows me to pull data out from my WordPress DB. Problem is that the DB is created by a forms plugin and the table structure is fixed:</p>
  
  <pre><code>+--------------+------------------+
  |Column        |Type              |
  +--------------+------------------+
  |submit_time   |decimal(16,4)     |
  +--------------+------------------+
  |form_name     |varchar(127) NULL |
  +--------------+------------------+
  |field_name    |varchar(127) NULL |
  +--------------+------------------+
  |field_value   |longtext NULL     |
  +--------------+------------------+
  |field_order   |int(11) NULL      |
  +--------------+------------------+
  |file          |longblob NULL     |
  +--------------+------------------+
  </code></pre>
  
  <p>Therefore the values that appear in the DB is not ideal for reading or editing.</p>
  
  <pre><code>+------------+------------+-----------------+------------------+------------+-----+
  |submit_time |form_name   |field_name       | field_value      |file_order  |file | 
  +------------+------------+-----------------+------------------+------------+-----+
  |15052703120 |Submissions |your-name        | Leonard Chia     |9999        |file | 
  +------------+------------+-----------------+------------------+------------+-----+
  |15052703120 |Submissions |your-email       | leonard@mail     |8           |file | 
  +------------+------------+-----------------+------------------+------------+-----+
  |15052703120 |Submissions |status           | Pending Start    |9           |file | 
  +------------+------------+-----------------+------------------+------------+-----+
  |15052703120 |Submissions |location         | DD1 Classroom    |7           |file | 
  +------------+------------+-----------------+------------------+------------+-----+
  </code></pre>
  
  <p>I was trying to achieve this:</p>
  
  <pre><code>+-------------+-------------+---------------+---------------+
  | Requestor   | Email       | Location      | Status        |
  +-------------+-------------+---------------+---------------+
  |Leonard Chia | leonard@mail| DD1 Classroom | Pending Start |
  +-------------+-------------+---------------+---------------+
  |Sng Yeekia   | yeekias@mail| DD2 Classroom | Pending Spare |
  +-------------+-------------+---------------+---------------+
  |Gabriel Lee  | gabriel@mail| SL1 Classroom | Completed     |
  +-------------+-------------+---------------+---------------+
  </code></pre>
  
  <p>I tried to transpose or pivot the tables using <code>CASE WHEN IF</code> but it doesn't work. Also tried to use <code>MAX</code> and <code>UNION ALL</code>but same results.</p>
  
  <p>Can anyone here point out to me what's wrong with my coding?</p>
  
  <p>This is my code:</p>
  
  <pre><code>SELECT wpc5_cf7dbplugin_submits.submit_time
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'your-name', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Requestor,
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'campus', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Campus,
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'location', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Defect Location,
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'your-message', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Defect Description,
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'menu-priority', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Priority,
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'work-done', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Action Taken,
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'assigned-to', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Assigned To,
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'category', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Category,
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'file-photo', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Photo
     MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'status', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Status
  FROM wpc5_cf7dbplugin_submits
  GROUP BY wpc5_cf7dbplugin_submits.submit_time
  </code></pre>
  
  <p>The error returned from wpdatatables:</p>
  
  <blockquote>
    <p>No results found. Please check if this query is correct! Table Constructor needs a query that returns data to build a wpDataTable.
    Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'your-name', wpc5_cf7dbplugin_subm' at line 1</p>
  </blockquote>
  
  <p><strong><em>Update</em></strong></p>
  
  <p>Edited the code to below</p>
  
  <pre><code>    SELECT wpc5_cf7dbplugin_submits.submit_time,
     CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'your-name' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Requestor,
     CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'campus' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Campus,
     CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'location' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Location,
     CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'your-message' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Description,
     CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'menu-priority' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Priority,
     CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'work-done' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Action,
     CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'assigned-to' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Assigned,
     CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'category' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Category,
    CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'file-photo' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Photo,
    CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'status' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Status
  FROM wpc5_cf7dbplugin_submits
  GROUP BY wpc5_cf7dbplugin_submits.submit_time
  </code></pre>
  
  <p>The table had more NULL then fields, most of the filled up fields are only from the 1st column, which is rather weird.</p>
  
  <pre><code>+-------------+----------+----------+----------+
  | Requestor   | Campus   | Location | Status   |
  +-------------+----------+----------+----------+
  |Leonard Chia | NULL     | NULL     | NULL     |
  +-------------+----------+----------+----------+
  |NULL         | NULL     | NULL     | NULL     |
  +-------------+----------+----------+----------+
  |Gabriel Lee  | NULL     | NULL     | NULL     |
  +-------------+----------+----------+----------+
  </code></pre>
  
  <p>What has gone wrong??</p>