将MYSQL数据库的行转换为列

Question

I currently have a database that has the following format:

ID    |    Year    |    Name    |    Address    |    Penalty
1     |    2015    |   Test 1   |    Test 1     |     0.2 
2     |    2014    |   Test 1   |    Test 1     |     0.3 
3     |    2013    |   Test 1   |    Test 1     |     0.4 
4     |    2015    |   Test 2   |    Test 2     |     0.5 
5     |    2014    |   Test 2   |    Test 2     |     0.6 
6     |    2015    |   Test 3   |    Test 3     |     0.7 

What I am trying to do is write a query that will generate a format similar to this:

ID    |    Name    |    Address    |    2015Penalty    |    2014Penalty    |    2013Penalty
1     |   Test 1   |    Test 1     |        0.2        |        0.3        |       0.4
2     |   Test 1   |    Test 1     |        0.5        |        0.6        |       0.2
3     |   Test 1   |    Test 1     |        0.7        |        0.8        |       0.3

Basically Instead of having a row for every year of each company, I need a row for each company and then a column with every year's penalty.

Is this possible to do? I would like to IF possible to do it entirely in SQL because the rows basically get echo'd out to a page and it becomes an Excel Spreadsheet:

while($row = mysqli_fetch_assoc($res)) {
    if(!$flag) {
        // display field/column names as first row
        echo implode("\t", array_keys($row)) . "
";
        $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "
";
}

I have been trying to see if there is a way to do it in PHP but I'm not really sure how to do that either so any help is greatly appreciated. If any further clarification is necessary, please let me know.

EDIT

I probably should have been more specific, I would like it so that the columns would be automatically generated based on the years, as every year more data gets imported and then the SQL will have to get updated as well. If there is a way to automate this, it would be much better.

Answer 1

You can do this using conditional aggregation:

select id, name, address,
       max(case when year = 2015 then penalty end) as penalty2015,
       max(case when year = 2014 then penalty end) as penalty2014
from table t
group by id, name, address;