sql - Mysql single column result to multiple column result -

sql - Mysql single column result to multiple column result -

i have problem mysql query, problem have next table:

id, rep, val dates 1 rep1 200 06/01/2014 2 rep2 300 06/01/2014 3 rep3 400 06/01/2014 4 rep4 500 06/01/2014 5 rep5 100 06/01/2014 6 rep1 200 02/06/2014 7 rep2 300 02/06/2014 8 rep3 900 02/06/2014 9 rep4 700 02/06/2014 10 rep5 600 02/06/2014

and want result this:

rep 01/06/2014 02/06/2014 rep1 200 200 rep2 300 300 rep3 400 900 rep4 500 700 rep5 100 600

thank much!

you seem want val each of dates.

assuming dates interested in fixed can follows. output date column check of row matches date column. if utilize value of val , if not utilize 0. sum resulting values, grouping rep. have assumed fixed format of date.

select rep, sum(if(dates='2014/06/01'), val, 0) '2014/06/01', sum(if(dates='2014/06/02'), val, 0) '2014/06/02' sometable grouping rep

or if wanted highest val each day

select rep, max(if(dates='2014/06/01'), val, 0) '2014/06/01', max(if(dates='2014/06/02'), val, 0) '2014/06/02' sometable grouping rep

if number of dates variable not direct way (as number of resulting columns vary). easiest manly in calling script based on following, giving 1 row per rep / possible date sum of values of val rep / date combination:-

select rep, sub0.dates, sum(if(sometable.dates=sub0.dates), val, 0) sometable cross bring together ( select distinct dates sometable ) sub0 grouping rep, sub0.dates

mysql sql