Geek asks and answers

MySQL: how to convert multiple rows to one row? in mysql

I want to convert several lines to one line depending on the week. It should look like this. Can anyone 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 |

The 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.

+3
source share
4 answers

You can do it as follows:

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 do not know if you want to AVG, SUM, MAXor MIN, but you can change the unit the way you want.

Useful links:

+1
source

if it is one table, then

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

This will give you a string, each with a weekly identifier and a comma, separated by characters

+1
source

" ", .

GROUP_CONCAT , .

0
source

You can 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 have a pivot table. You must add additional code to convert the data according to your needs. You may run into problems if using WEEK () over the years.

0
source

More articles:


All Articles