Mysql Pivot View

I have a mysql table as below

username    interval    totalmails
User1      10:00            3
User2      10:00            1
User3      10:00            1
User6      10:00            4
User7      10:00            4
User8      10:00            5
User9      10:00            2
User10     10:00            5
User2      11:00            4
User3      11:00            7
User5      11:00            4
User6      11:00            4
User8      11:00            4
User9      11:00            3
User4      9:00             9
User5      9:00             1
User7      9:00             7
User8      9:00             3

I am looking for a pivot view which would output as below:

Username       9:00        10:00      11:00      Grand Total
 User1                       3                       3
 User2                       1           4           5
 User3                       1           7           8
 User4           9                                   9
 User5           1                       4           5
 User6                       4           4           8
 User7           7           4                      11
 User8           3           5           4          12
 User9                       2           3           5
 Grand Total    20          25          26          71

Can this be done by one mysql query without using prepare and execute ?

Probably a select query

Currently i am doing it with php. below is mycode:

$data = array();
            $d    = array();
            $heading = array('Username');
            $c    = array('Grand Total' => 0);
            //$data['usernames']['username'] = 'Username';

            foreach ($res as $key => $value) {
                $data['tbl'][$value['username']][strtotime($value['interval'])] = $value['mails'];
                $data['tbl']['Grand Total'][strtotime($value['interval'])]      = ($data['tbl']['Grand Total'][strtotime($value['interval'])] + $value['mails']);
                //$data['tbl']['Grand Total']['Grand Total']                         = ($data['tbl']['Grand Total']['Grand Total'] + $value['mails'])/2;
                $data['intervals'][strtotime($value['interval'])] = $value['interval'];
                $data['usernames'][$value['username']]            = $value['username'];
            }

            foreach ($data['tbl'] as $key => $value) {
                $data['tbl'][$key]['Grand Total'] = array_sum($value);
            }

            //$data['tbl'] = array_merge($data['tbl'], $d);
            //$data['tbl'] = array_merge($data['tbl'], $c);
            array_unique($data['intervals']);
            array_unique($data['usernames']);
            ksort($data['intervals']);
            $data['intervals']['Grand Total'] = 'Grand Total';
            $data['usernames']['Grand Total'] = 'Grand Total';

            $this->load->library('table');
            $tmpl = array(
                'table_open'         => '<table class="table table-condensed table-striped table-bordered" id="sent_tbl">',
                'heading_row_start'  => '<tr>',
                'heading_row_end'    => '</tr>',
                'heading_cell_start' => '<th style="text-align: center;">',
                'heading_cell_end'   => '</th>',
                'row_start'          => '<tr class="odd gradeX">',
                'row_end'            => '</tr>',
                'cell_start'         => '<td style="text-align: center;">',
                'cell_end'           => '</td>',
                'row_alt_start'      => '<tr class="odd gradeX">',
                'row_alt_end'        => '</tr>',
                'cell_alt_start'     => '<td style="text-align: center;">',
                'cell_alt_end'       => '</td>',
                'table_close'        => '</table>'
            );
            $this->table->set_template($tmpl);
            $heading = array_merge($heading,array_values($data['intervals']));

            $this->table->set_heading($heading);

            foreach ($data['usernames'] as $uname => $unameval) {
                $row = array();
                $row = array(array('data'  => $unameval,
                                   'style' => 'text-align:left;'
                )
                );

                if (strlen($unameval) > 1) {
                    foreach ($data['intervals'] as $key => $value) {
                        if ($data['tbl'][$unameval][$key] > 0) {
                            array_push($row, round($data['tbl'][$unameval][$key], 1));
                        } else {
                            array_push($row, '');
                        }
                    }
                }

                //echo '<pre>' . print_r($row, TRUE) . '</pre>';

                $this->table->add_row($row);
            }

            echo '<strong>Hourly Total:</strong>';
            echo $this->table->generate();

Try out this:

function get()
 {
  $this->db->select("username,
    Avg(CASE WHEN interval ='10:00' THEN totalmail END) as [10:00],
    Avg(CASE WHEN interval ='11:00' THEN totalmail) END as [11:00],
    Avg(CASE WHEN interval ='12:00' THEN totalmail) END as [12:00],
    Sum(CASE WHEN interval ='10:00' THEN totalmail) END as [Grand_Total];


$this->db->from('your_table_name');
$this->db->group_by('username');

}

hope this will work for you...!!!
do let me know: thanks