Mysql Row and column conversions

1. Create a table

CREATE TABLE ' gas ' (' id ' INT (one) not null auto_increment, ' oil ' varchar) null DEFAULT null, ' district ' varchar (+) NULL Default NULL, ' price ' DOUBLE null default ' 0 ', PRIMARY KEY (' id ')) engine=innodbauto_increment=11;

2, the test data as follows, select * from the gas;

| ID | Oil | District | Price |
| 1 | #0 | Bao ' an | 11 |
| 2 | #0 | Futian | 12 |
| 3 | #0 | Lo Wu | 13 |
| 4 | #93 | Bao ' an | 21 |
| 5 | #93 | Futian | 22 |
| 6 | #93 | Lo Wu | 23 |
| 7 | #97 | Bao ' an | 31 |
| 8 | #97 | Futian | 32 |

| 9 | #97 | Lo Wu | 33 |

3, the column conversion:

Select Ifnull (oil, ' total ') as Oil,sum (if (district= ' Baoan ', price,0)) as Ba,sum (if (district= ' Fukuda ', price,0)) as Ft,sum (if ( District= ' Lo Wu ', price,0)) as Lh,sum (price) as Totalfrom Gasgroup by oil with rollup;

The results of the implementation are as follows:

| Oil | BA | FT | LH | Total |
| #0 | 11 | 12 | 13 | 36 |
| #93 | 21 | 22 | 23 | 66 |
| #97 | 31 | 32 | 33 | 96 |
| Total | 63 | 66 | 69 | 198 |

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Mysql Row and column conversions