Convert MySQL rows to columns

statistical data is required to generate a simple report tonight. The original table data is in the form of a single row. It is recommended to convert it to the List format. The method described on the internet is as follows:

For the final results, see:

+ ------- + ------ + ------- + -------- + ------------------- + --------- +
| Room | Rent | Water Fee | Electricity bill | Health fee | TV fee | Network fee | recorded time | Total |
+ ------- + ------ + ------- + -------- + ------------------- + --------- +
| 0201 | 400 | 0 | 0 | 10 | 0 | 0 | 11:01:21 | 410 |
| 0204 | 150 | 0 | 0 | 10 | 0 | 0 | 11:00:21 | 160 |
| 0206 | 150 | 0 | 0 | 10 | 0 | 0 | 18:02:50 | 160 |
| 0302 | 350 | 40.92 | 18.91. | 20 | 50 | 50 | 01:45:23 | 529.83 |
| 0306 | 150 | 0 | 0 | 10 | 0 | 0 | 11:23:15 | 160 |
| 0308 | 200 | 0 | 0 | 10 | 0 | 0 | 22:26:41 | 210 |
| Total | 1400 | 40.92 | 18.91 | 70 | 50 | 50 | 22:26:41 | 1629.83 |
+ ------- + ------ + ------- + -------- + ------------------- + --------- +

For the implemented SQL statements, see:

Mysql> Select
-> Ifnull (roomno, 'Total') as room,
-> Sum (if (feename = 'rent ', feemoney, 0) as rent,
-> Sum (if (feename = 'watermark', feemoney, 0) as water fee,
-> Sum (if (feename = 'Electricity Bill ', feemoney, 0) as electricity bill,
-> Sum (if (feename = 'health payby', feemoney, 0) as health fee,
-> Sum (if (feename = 'TV payby', feemoney, 0) as video fee,
-> Sum (if (feename = 'network payby', feemoney, 0) as network fee,
-> Ifnull (cdate, cdate) as record time,
-> Sum (if (feename = 'Total', feemoney, 0) as total
-> From (
-> Select No. roomno as roomno, ifnull (F. feename, 'Total') as feename, sum (F. feemoney) as feemoney, F. createdate as cdate
-> From roomnoinfo no, then f
-> Where no. Bid = 1 and No. beempty = 'full' and No. roomno = f. roomno and
-> F. createdate <'2014-03-31 'and F. createdate> '2014-01-01'
-> Group by roomno, feename
-> With Rollup
-> Having roomno is not null
-> ) As
-> Group by roomno
-> With rollup;

The table format of the source data is as follows:

Mysql> select roomno, createdate, feename, feemoney from region where Bid = 1;
+ -------- + ------------------- + --------- + ---------- +
| Roomno | createdate | Feename | feemoney |
+ -------- + ------------------- + --------- + ---------- +
| 0101 | 22:41:24 | rent | 200 |
| 0101 | 22:41:24 | Water Fee | 13.2 |
| 0101 | 22:41:24 | electricity fee | 6.1 |
| 0102 | 17:01:52 | rent | 150 |
| 0102 | 17:01:52 | Water Fee | 145.2 |
| 0102 | 17:01:52 | electricity fee | 67.1 |
| 0102 | 17:01:52 | health fee | 10 |
| 0204 | 11:00:21 | rent | 150 |
| 0204 | 11:00:21 | Water Fee | Null |
| 0204 | 11:00:21 | electricity fee | Null |
| 0204 | 11:00:21 | health fee | 10 |
| 0206 | 18:02:50 | rent | 150 |
| 0206 | 18:02:50 | Water Fee | Null |
| 0206 | 18:02:50 | electricity fee | Null |
| 0206 | 18:02:50 | health fee | 10 |
| 0302 | 01:42:35 | rent | 150 |
| 0302 | 01:42:35 | Water Fee | 40.92 |
| 0302 | 01:42:35 | electricity fee | 18.91 |
| 0302 | 01:42:35 | health fee | 10 |
| 0302 | 01:45:23 | health fee | 10 |
| 0302 | 01:45:23 | rent | 200 |
| 0302 | 01:45:23 | Network fee | 50 |
| 0302 | 01:45:23 | TV fee | 50 |
| 0306 | 11:23:15 | rent | 150 |
| 0306 | 11:23:15 | Water Fee | Null |
| 0306 | 11:23:15 | electricity fee | Null |
| 0306 | 11:23:15 | health fee |