Mysql行轉換為列

今晚需要統計資料產生簡易報表，由原表格式資料是單行的形式，最好轉換為列表格式，由網上介紹方法實現如下：

希望獲得的最終效果見下：

+-------+------+-------+-------+--------+--------+--------+---------------------+---------+
| 房間  | 房租 | 水費  | 電費  | 衛生費 | 電視費 | 網路費 | 記錄時間            | total   |
+-------+------+-------+-------+--------+--------+--------+---------------------+---------+
| 0201  |  400 |     0 |     0 |     10 |      0 |      0 | 2011-02-08 11:01:21 |     410 |
| 0204  |  150 |     0 |     0 |     10 |      0 |      0 | 2011-02-08 11:00:21 |     160 |
| 0206  |  150 |     0 |     0 |     10 |      0 |      0 | 2011-01-16 18:02:50 |     160 |
| 0302  |  350 | 40.92 | 18.91 |     20 |     50 |     50 | 2011-01-18 01:45:23 |  529.83 |
| 0306  |  150 |     0 |     0 |     10 |      0 |      0 | 2011-02-08 11:23:15 |     160 |
| 0308  |  200 |     0 |     0 |     10 |      0 |      0 | 2011-03-28 22:26:41 |     210 |
| total | 1400 | 40.92 | 18.91 |     70 |     50 |     50 | 2011-03-28 22:26:41 | 1629.83 |
+-------+------+-------+-------+--------+--------+--------+---------------------+---------+

實現的SQL語句見下：

mysql>  SELECT
    ->      IFNULL(RoomNo,'total') AS 房間,
    ->      SUM(IF(FeeName='房租',FeeMoney,0)) AS 房租,
    ->      SUM(IF(FeeName='水費',FeeMoney,0)) AS 水費,
    ->      SUM(IF(FeeName='電費',FeeMoney,0)) AS 電費,
    ->      SUM(IF(FeeName='衛生費',FeeMoney,0)) AS 衛生費,
    ->      SUM(IF(FeeName='電視費',FeeMoney,0)) AS 電視費,
    ->      SUM(IF(FeeName='網路費',FeeMoney,0)) AS 網路費,
    ->      IFNULL(CDate, CDate) AS 記錄時間,
    ->      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, Fee f
    -> where no.bid=1 and no.beempty='full' and no.RoomNo=f.RoomNo and
    -> f.CreateDate < '2011-03-31' and f.CreateDate >'2011-01-01'
    -> GROUP BY RoomNO, FeeName
    -> WITH ROLLUP
    -> HAVING RoomNO IS NOT NULL
    ->  ) AS A
    ->  GROUP BY RoomNo
    ->  WITH ROLLUP;

來源資料的表格式如下：

mysql> select RoomNo, CreateDate, FeeName, FeeMoney from Fee where bid=1;
+--------+---------------------+---------+----------+
| RoomNo | CreateDate          | FeeName | FeeMoney |
+--------+---------------------+---------+----------+
| 0101   | 2011-01-15 22:41:24 | 房租    |      200 |
| 0101   | 2011-01-15 22:41:24 | 水費    |     13.2 |
| 0101   | 2011-01-15 22:41:24 | 電費    |      6.1 |
| 0102   | 2011-01-16 17:01:52 | 房租    |      150 |
| 0102   | 2011-01-16 17:01:52 | 水費    |    145.2 |
| 0102   | 2011-01-16 17:01:52 | 電費    |     67.1 |
| 0102   | 2011-01-16 17:01:52 | 衛生費  |       10 |
| 0204   | 2011-02-08 11:00:21 | 房租    |      150 |
| 0204   | 2011-02-08 11:00:21 | 水費    |     NULL |
| 0204   | 2011-02-08 11:00:21 | 電費    |     NULL |
| 0204   | 2011-02-08 11:00:21 | 衛生費  |       10 |
| 0206   | 2011-01-16 18:02:50 | 房租    |      150 |
| 0206   | 2011-01-16 18:02:50 | 水費    |     NULL |
| 0206   | 2011-01-16 18:02:50 | 電費    |     NULL |
| 0206   | 2011-01-16 18:02:50 | 衛生費  |       10 |
| 0302   | 2011-01-18 01:42:35 | 房租    |      150 |
| 0302   | 2011-01-18 01:42:35 | 水費    |    40.92 |
| 0302   | 2011-01-18 01:42:35 | 電費    |    18.91 |
| 0302   | 2011-01-18 01:42:35 | 衛生費  |       10 |
| 0302   | 2011-01-18 01:45:23 | 衛生費  |       10 |
| 0302   | 2011-01-18 01:45:23 | 房租    |      200 |
| 0302   | 2011-01-18 01:45:23 | 網路費  |       50 |
| 0302   | 2011-01-18 01:45:23 | 電視費  |       50 |
| 0306   | 2011-02-08 11:23:15 | 房租    |      150 |
| 0306   | 2011-02-08 11:23:15 | 水費    |     NULL |
| 0306   | 2011-02-08 11:23:15 | 電費    |     NULL |
| 0306   | 2011-02-08 11:23:15 | 衛生費  |