0

i have a table 'operation_record' like this:

date operation
1-1　　　a
1-1　　　b
1-1　　　b
1-1　　　a
1-1　　　a
1-1　　　c
1-1　　　a
1-2　　　c
1-2　　　b
1-2　　　c
1-2　　　a
1-2　　　a
1-2　　　b
1-2　　　c

and what I want is like this:

data operation_a operation_b operation_c
1-1　　　　4　　　　　　2　　　　　1
1-2　　　　2　　　　　　2　　　　　3

if the operations is fixed, i can use things like this:

select o.uc, ,a.rc operaiton_a, b.rc operaiton_b, c.rc operaiton_c from
(select date uc,count(1) rc from operaion_record group by date) o 
left join 
(select date uc,count(1) rc from operaion_record where operation='a' group by date)a on o.uc=a.uc
left join 
(select date uc,count(1) rc from operaion_record where operation='b' group by date)b on o.uc=b.uc
left join 
(select date uc,count(1) rc from operaion_record where operation='c' group by date)b on o.uc=c.uc

but the problem is, the operation maybe varied.

though ,i can get these operations by

select distinct(operation) from operation_record

then, how can I make this values as new columns and got those counts?

pumbaac
  • 3
  • 4
  • use another table in which have two fields **id** **operation_name** and in **operation_record** store **operation_id** instead of **operation** and use join for extract data – Bhavin Shah Sep 08 '15 at 09:46
  • you're trying to dynamically pivot the data using MySQL. MySQL is not very good at this. Have a look here for further inspiration: https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns/12005676#12005676 – Tom Mac Sep 08 '15 at 10:29
  • @TomMac thanks a lot for the refer link! and I also learned what 'pivot' means :) – pumbaac Sep 08 '15 at 11:08

0 Answers0