MySQL : How to convert multiple row to single row? in mysql

Question

I want to convert multiple rows to a single row, based on week. It should look like the following. Can any one help me?

id      |   Weight   |  Created   |
 1      |    120     | 02-04-2012 |
 2      |    110     | 09-04-2012 |
 1      |    100     | 16-04-2012 |
 1      |    130     | 23-04-2012 |
 2      |    140     | 30-04-2012 |
 3      |    150     | 07-05-2012 |

Result should look like this:

id      |   Weight_week1  | Weight_week2  |  weight_week3  | weight_week4  |
 1      |     120         |     100       |      130       |               |
 2      |     110         |     140       |                |               |
 3      |     150         |               |                |               |

Thanks in advance.

Answer 1

if this a single table then

SELECT GROUP_CONCAT(weight) as Weight,
        WEEK(Created) as Week
Group by Week(Created)

This will give you a row each having week id and comma seperated whights