MySQL many to many table to table matrix

Question

Hi,

I have a many to many table in MySQL - it can look something like:

A | B
1 | 1
1 | 2
1 | 3
3 | 4
3 | 5
4 | 1
4 | 2
4 | 5

etc.

You'll notice that the numbers are not always contiguous, (A=1,3,4..) - I am looking for the fastest way to turning this into a matrix table, ignoring columns and rows with no data (e.g. A=2). What I have working is painfully slow as I am taking each row into a separate array in php, going through a nested while loop, where it checks if the key for array A and array B match the current index:

while ($i <= $max_A) {
    if (in_array($i, $array_A)) {
     print ("<tr>");

     while ($j <= $max_B) {
      if (in_array($j, $array_B)) {
       print ("<td>");
       if (in_matrix($i, $j, $array_A, $array_B)) {
        print ("1");
       } else {
        print ("0");
       }
       print ("</td>");
      }
      $j++;
     }
     print ("</tr>\n");
     $j = $min_B;
    }
    $i++;
}

function in_matrix($search_value_A, $search_value_B, $array_A, $array_B) {
    // Store the array keys matching search
    $keys_A = array_keys($array_A, $search_value_A);
    $keys_B = array_keys($array_B, $search_value_B);
    foreach ($keys_A as $value) {
     if (in_array($value, $keys_B)) {
      return true;
     }
    }
    return false;
}

Perhaps there is more I can do MySQL side or speed up the search function - I tried finding a way to searching a two-dimensional array (e.g. return true if Column A = x AND Column B = y in a given row).

Thanks

Answer 1

It's called pivoting the data when it's done in SQL, because you're changing row into columnar data:

   SELECT t.a,
          MAX(CASE WHEN t.b = 1 THEN 'x' ELSE NULL END),
          MAX(CASE WHEN t.b = 2 THEN 'x' ELSE NULL END),
          MAX(CASE WHEN t.b = 3 THEN 'x' ELSE NULL END),
          MAX(CASE WHEN t.b = 4 THEN 'x' ELSE NULL END),
          MAX(CASE WHEN t.b = 5 THEN 'x' ELSE NULL END),
     FROM TABLE t
GROUP BY t.a

The CASE statement is similar in structure to a SWITCH statement, but doesn't support fall through. You'll have to define a CASE statement for each row value you want to output as a column.