Converting rows of MYSQL database into columns

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)) . "\r\n";
        $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\r\n";
}

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.

What you describe is called pivoting, for your information. — O. Jones
– O. Jones, Commented Jun 24, 2015 at 1:52
I had no idea! Somewhat of a beginner when it comes to SQL queries, going to google that now, thanks. — Brandon Shega
– Brandon Shega, Commented Jun 24, 2015 at 1:53

Gordon Linoff · Accepted Answer · 2015-06-24 01:58:42Z

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;

answered Jun 24, 2015 at 1:58

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brandon Shega Over a year ago

Will this work if the year is not guaranteed to be there? Also, do I have to write a new max for every possible year or is there a way to automate that?

Gordon Linoff Over a year ago

@BrandonShega . . . In fact, yes. It will give a value of NULL when the year is not there. You could add else 0 if you wanted 0 instead.

Brandon Shega Over a year ago

I edited my question above, apologies, but is there anyway for it to generate the column heading based on the year, rather than me create a case for every year?

Gordon Linoff Over a year ago

That is a very different question. Google: "MySQL dynamic pivot".

Collectives™ on Stack Overflow

Converting rows of MYSQL database into columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related