Convert columns to rows dynamically with records in Mysql - PHP

Question

I have this in my database:

 Stud_id        subject      Total
 Abc12          Eng           60
 Abc13          Eng           40    
 Abc12          Math          70
 Abc13          Math          50

This is the output i expect:

 Stud_id        Eng       Math

 Abc12          60        70
 Abc13          40        50

If there was a large amount of records, how could i do this in bulk amount? performance and reliability is much needed!

I tried the following code:

<?php

            ...
            $sql = "select   
              GROUP_CONCAT(DISTINCT
                 CONCAT(
                  'ifnull(SUM(case when sub_code = ''',
                  sub_code,
                  ''' then total end),0) AS `',
                  sub_code, '`'
                )
                ) INTO @sql
                FROM scores_tbl;
            
           (stud_id) as USERID
           , sum(total) as Total,
           ('') as '%'
       
           FROM scores_tbl where class_name='JSS1' AND GROUP by stud_id";
            $result = $db->multi_query($sql);

            if ($err=mysqli_error($db)) { echo $err."<br><hr>"; }

            if ($result) {
              do {
              if ($res = $db->store_result()) {
                  echo "<table width=100% border=1><tr>";
    
              // printing table headers
                  for($i=0; $i<mysqli_num_fields($res); $i++)
                  {
                      $field = mysqli_fetch_field($res);
                  echo "<td bgcolor=lightgray><b>{$field->name}</b></td>";
                  }
                  echo "</tr>\n";
    
                  // printing table rows
                  while($row = $res->fetch_row())
                  {
                      echo "<tr>";
                      foreach($row as $cell) {
                            if ($cell === NULL) { $cell = '(null)'; }
                        echo "<td>$cell</td>";
                      }
                      echo "</tr>\n";
                  }
                  $res->free();
                  echo "</table>";
        
                }
              } while ($db->more_results() && $db->next_result());
            }
            $db->close();
            ?>

It doesn't give any errors, but doesn't work either

Answer 1

Thank you everyone here. I later solved the problem by myself using the similar solution posted on this platform. This is how I solved it:

$MySQLiconn->query('SET @sql = NULL');
$MySQLiconn->query("
SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
     'ifnull(SUM(case when sub_code = ''',
      sub_code,
      ''' then total end),0) AS `',
      sub_code, '`'
    )
   ) INTO @sql
FROM scores_tbl;"

);
               
$MySQLiconn->query("SET @sql = CONCAT('SELECT 
stud_id, ', @sql, ' FROM scores_tbl GROUP BY 
stud_id');");
$MySQLiconn->query("PREPARE stmt FROM @sql");

$res = $MySQLiconn->query("EXECUTE stmt");