Use data field from table as column header?

Question

I have created a php page with mysql backend to show results of a small table containing Staff Name, Month, Orders as such:

<table border="1" cellpadding="1" cellspacing="1">
   <thead>
        <tr>
    <th scope="col">Name</th>
    <th scope="col">Month</th>
    <th scope="col">Cases</th>    
  </tr>
   </thead>
  <?php do { ?>
  <tbody>
    <tr>
      <td><?php echo $row_rsChart2['ContactFullName']; ?></td>
      <td><?php echo $row_rsChart2['Month']; ?></td>
      <td><?php echo $row_rsChart2['Cases']; ?></td>
    </tr>
    </tbody>
    <?php } while ($row_rsChart2 = mysql_fetch_assoc($rsChart2)); ?>
</table>

Is it possible I could use the month columns as a header, displaying Jan, Feb, March etc from left to right in column header and cases for each month as data?

Jan - Feb - Mar
User1 100 47 89
user2 86 67 134

With the default layout it displays as:

Name - Month - Cases
User1   Jan    100
User1   Feb    47
User2   Jan    67

Answer 1

MySQL doesn't have any native support for pivoting data like this. You have a few options:

1. Construct a rather horrible MySQL query to perform the pivoting operation manually:

   SELECT *
     FROM (
       SELECT StaffName, SUM(NumOrders) AS January
       FROM Orders
       WHERE Month = 'January'
       GROUP BY StaffName
     ) AS tJan
     NATURAL JOIN (
       SELECT StaffName, SUM(NumOrders) AS February
       FROM Orders
       WHERE Month = 'February'
       GROUP BY StaffName
     ) AS tFeb
     NATURAL JOIN (
       -- etc.
   

   If you choose to go down this path, you can make your life slightly easier by generating this query automatically, using either a looping construct in PHP or a prepared statement in MySQL.

2. Do the above as a one-off operation so that the structure of your MySQL database is changed to more closely reflect this desired layout (easy once table is converted, but may impact other uses of the database):

   CREATE TABLE NewOrders (PRIMARY KEY('StaffName'))
   SELECT * FROM (
     -- etc. as above
   

   Alternatively, you can create a VIEW which is a sort of "virtual table" structured in this way based on the underlying table.

3. Pivot the data manually in PHP (tedious).