Pivot table rows to columns

I have this table:

CREATE TABLE Sales (empID INT, yr SMALLINT, sales DECIMAL(10,2));

INSERT sales VALUES

(1, 2005, 12000),(1, 2006, 18000),(1, 2007, 25000),

(2, 2005, 15000),(2, 2006, 6000),(3, 2006, 20000),(3, 2007, 24000);

and this query in mysql:

  1. In an inner query, write one aggregating expression per reporting column,

  2. In an outer query, build the horizontal sums:

SELECT

IFNULL(empId,‘Totals’) AS EmpId, – outer query labels rollup row

sums.2005, sums.2006, sums.2007, – and calculates horizontal sums

sums.2005 + sums.2006 + sums.2007 AS Sums

FROM ( – inner query groups by employee

SELECT – with an expression for each column

EmpID, 


SUM(IF(Yr=2005,sales,0)) As '2005', 


SUM(IF(Yr=2006,sales,0)) As '2006', 


SUM(IF(Yr=2007,sales,0)) As '2007' 

FROM Sales

GROUP BY EmpID WITH ROLLUP

) AS sums;

And want to show the following table:

±-------±---------±---------±---------±----------+

| EmpId | 2005 | 2006 | 2007 | Sums |

±-------±---------±---------±---------±----------+

| 1 | 12000.00 | 18000.00 | 25000.00 | 55000.00 |

| 2 | 15000.00 | 6000.00 | 0.00 | 21000.00 |

| 3 | 0.00 | 20000.00 | 24000.00 | 44000.00 |

| Totals | 27000.00 | 44000.00 | 49000.00 | 120000.00 |

±-------±---------±---------±---------±----------+

With this code:


<?php

$provider = new SqlDataProvider([

	'sql' => 'SELECT  

  IFNULL(empId,"Totals") AS EmpId,       -- outer query labels rollup row 

  sums.2005, sums.2006, sums.2007,       -- and calculates horizontal sums 

  sums.2005 + sums.2006 + sums.2007 AS Sums 

FROM (                                   -- inner query groups by employee 

  SELECT                                 -- with an expression for each column 

    EmpID, 

    SUM(IF(Yr=2005,sales,0)) As `2005`, 

    SUM(IF(Yr=2006,sales,0)) As `2006`, 

    SUM(IF(Yr=2007,sales,0)) As `2007` 

  FROM Sales 

  GROUP BY EmpID WITH ROLLUP 

) AS sums',

	

    //'params' => [':status' => 1],

    //'totalCount' => $count,

    'pagination' => [

        'pageSize' => 10,

    ],

    'sort' => [

        'attributes' => [

            'EmpID',

            //'view_count',

            //'created_at',

        ],

    ],

]);


// returns an array of data rows

$model = $provider->getModels();

print_r($model);

?>

<!-- Main content -->

<section class="invoice">

     <table  class="table" >           

                <tr style= "background-color: #bfcfd2" ><td colspan="14"><b>Total Emp By Year</b></td></tr>                    

                <tr style= "background-color: #F2F2F2"><td>EMP_ID/Year</td>

                        <td>2005</td>

                        <td>2006</td>

                        <td>2007</td>

                        <td>Total</td> </tr>

 <?php                

                //total colums print with show university

                $dataList=ArrayHelper::map($model, 'EmpId', 'EmpId');

                foreach ($dataList as $key => $value) {

                    echo '<tr><td>'.$value.'</td>';

                    for($i=1;$i<=3;$i++){

                        echo '<td id='.$value.$i.'>0</td>';

                    }

                    echo '<td style= "background-color: #4CAF50" id='.$value.'>0</td></tr>';  //for print row toatal.

                }

                 //bootam row.

                //1.Display counts, 2.row total, 3.column total, 4. Grand total. 

                foreach ($model as $key => $value) {                    

                    echo "<script> document.getElementById('".$value['EmpId'].$value['Sums']."').innerHTML = ".$value['Sums']." </script>";                    

                    echo "<script> "                    

                    . "document.getElementById('".$value['EmpId']."').innerHTML = parseInt(document.getElementById('".$value['EmpId']."').innerHTML) + ". $value['Sums'].";"

                    . "document.getElementById('sums').innerHTML = parseInt(document.getElementById('sums').innerHTML) + ". $value['Sums']        

                    . "</script>";                                        

                }

                ?>

                </table>            

</section>

The table is showing only the Totals of years, not the sums for particular months.