MySQL: Convert Column Values to Headers with Totals

Question

I have a MySQL table 'dogs' structured like so:

AgLevel  DogType  Total
1        1        10
2        1        29
3        1        30
1        2        20
2        2        14
3        2        28

So this says there are 10 dogs of type 1 with an aglevel of 1, 29 dogs of type 1 with an ag level of 2 etc.

I would like to reformat it like so:

DogType   Ag1   Ag2   Ag3
1         10    29    30
2         20    14    28

So here again we can see there are 10 dogs of type 1 with an aglevel of 1.

How would I go about this?

Answer 1

select dogType, 
       sum(case when agLevel = 1 then total else 0 end) as ag1,
       sum(case when agLevel = 2 then total else 0 end) as ag2,
       sum(case when agLevel = 3 then total else 0 end) as ag3
from dogs
group by dogType