mySQL reformat query results into pivot table

Question

May I ask for a little help with the following please ?

My query results are returned like ...

cDate       posId    val
14/01/2011  0        200
14/01/2011  1        199
…       
14/01/2011  8        198
14/01/2011  9        197
06/03/2015  0        264
06/03/2015  1        264
…       
06/03/2015  8        261
06/03/2015  9        264

Is it possible to 'reformat' the output like this ...

Position    14/01/2011   06/03/2015
0           200          264
1           199          264
2           195          264
3           192          264
4           190          264
5           195          263
6           197          261
7           199          260
8           198          261
9           197          264

I'd like to read through these rows in php and put together a csv file that I can download.

I think this Crosstabs section at artfulsoftware.com may contain the answer for me, but I'm just not 'getting it' right now.

May I ask you for a pointer or two please ? Am I heading in the right direction with Pivot Tables ?

Thank you.

And the dates may be of any numbers ? Right now in your example there are only 2. — Abhik Chakraborty, Apr 14 '15 at 12:52
Google "mysql pivot" if you know that there are two columns. Otherwise, google "mysql dynamic pivot". — Gordon Linoff, Apr 14 '15 at 12:56
@OP: Solution for your query is at http://stackoverflow.com/a/12630301/767881 — Ravinder Reddy, Apr 14 '15 at 12:59
@ Abhik Chakraborty. I was trying to keep thing simple for myself. So I was just using 2 dates presently. — Johnny, Apr 14 '15 at 13:04
Gordon Linoff. Thanks for the pointers, I will google "mysql dynamic pivot". — Johnny, Apr 14 '15 at 13:04

mySQL reformat query results into pivot table

0 Answers0