0

I am trying to format a query from a table to look like a matrix that has the date down the left and the results of 3 grouped values under 4 periods during the day. The Table looks like this:

dated     |timed   |beforefood|R  |C |I|
2017-01-01|08:16:00|breakfast |7.3|35|7|
2017-01-01|12:10:00|lunch     |5.3|56|9|
2017-01-01|17:17:00|dinner    |6.8|95|6|
2017-01-01|22:12:00|bedtime   |7.9|0 |0| 

2017-01-02|08:33:00|breakfast |5.6|35|6|
2017-01-02|12:04:00|lunch     |6.8|40|7| 
2017-01-02|17:00:00|dinner    |8.6|50|6|
2017-01-02|22:10:00|bedtime   |9.9|0 |0|

2017-01-03|07:33:00|breakfast |6.2|35|6|
2017-01-03|12:00:00|lunch     |7.3|56|8|
2017-01-03|17:51:00|dinner    |6.4|10|5|
2017-01-03|22:57:00|bedtime   |8.4|0 |0|

To be shown as a Matrix
Date        Breakfast   Lunch   Dinner  Bed
2017-01-01  R-C-I       R-C-I   R-C-I   R-C-I
2017-01-02  R-C-I       R-C-I   R-C-I   R-C-I
2017-01-03  R-C-I       R-C-I   R-C-I   R-C-I

In the example above the date 2017-01-01 comes out in the correct order Breakfast --> Beditime However 2nd and 3rd come out with the RCI group in the correct order but back to front Bedtime --> Breakfast so breakfast is under bedtime, lunch in under dinner, dinner is under lunch and bedtime is under breakfast

The 4th line is correct and the 5th and 6th are back to front and this is the continuous pattern

Select statement used

SELECT  `dated` , GROUP_CONCAT( `R` , ' ', `C` , ' ', `I` ) AS Readings
FROM  `My_Test` 
AND `beforefood` =  'breakfast'
OR  `beforefood` =  'lunch'
OR  `beforefood` =  'dinner'
OR  `beforefood` =  'bedtime'
GROUP BY  `dated`
Dharman
  • 30,962
  • 25
  • 85
  • 135
FireNet
  • 11
  • 2
  • Changed your tag to _mysql_ from _php_ and _mysqli_. Neither are applicable to this question as the code in question is raw SQL. – War10ck Jan 23 '17 at 21:39
  • Thanks War10ck I have just noticed that the AND should be a WHERE – FireNet Jan 23 '17 at 21:42
  • What you are trying to do is called a pivot table. To point you in the right direction, have a look e.g. [here](http://stackoverflow.com/questions/7674786/mysql-pivot-table). – Solarflare Jan 23 '17 at 21:44
  • You could also do this using `JOIN`. Select the `breakfast` row and `LEFT JOIN` the other 3 row values. Then you only return 1 row per date. – Sean Jan 23 '17 at 21:46
  • Thanks Solarflare and Sean for a quick reply the pivot table looks like the style that I am after so will give it a try. – FireNet Jan 23 '17 at 21:51
  • Mysqli is a PHP API, so I'm a bit confused – Strawberry Jan 23 '17 at 23:08

0 Answers0