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

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

You could do it like this:

SELECT
    t.id,
    SUM(CASE WHEN WeekNbr=1 THEN Table1.Weight ELSE 0 END) AS Weight_week1,
    SUM(CASE WHEN WeekNbr=2 THEN Table1.Weight ELSE 0 END) AS Weight_week2,
    SUM(CASE WHEN WeekNbr=3 THEN Table1.Weight ELSE 0 END) AS Weight_week3,
    SUM(CASE WHEN WeekNbr=4 THEN Table1.Weight ELSE 0 END) AS Weight_week4
FROM
    (
    SELECT  
        (
           WEEK(Created, 5) - 
           WEEK(DATE_SUB(Created, INTERVAL DAYOFMONTH(Created) - 1 DAY), 5) + 1 
        )as WeekNbr,
        Table1.id,
        Table1.Weight,
        Table1.Created
    FROM
        Table1
    ) AS t
GROUP BY
    t.id

I don't know if you want a AVG,SUM,MAX or MIN but you can change the aggregate to what you want.

Useful references:

• Function for week of the month in mysql

you cannot create fields on the fly like that but you can group them.

use GROUP_CONCAT to deliver results with a delimiter that you can separate on later.

You could also do this:

SELECT id, created, weight, (
    SELECT MIN( created ) FROM weights WHERE w.id = weights.id
) AS `min` , round( DATEDIFF( created, (
   SELECT MIN( created )
FROM weights
WHERE w.id = weights.id ) ) /7) AS diff
FROM weights AS w
ORDER BY id, diff

This code does not do pivot table. You should add some additional code to convert the data to your needs. You may run into trouble if you use WEEK() because of the years.