mysql - How to create extra columns use column value? -

i'm having following problem

i have table this

+------+---------+------------+ | imei | channel |  sys_date  | +------+---------+------------+ | 1111 |unknown01| 2013-04-15 | | 2222 |unknown02| 2013-04-15 | | 3333 |unknown03| 2013-04-15 | | 4444 |unknown01| 2013-04-16 | | 5555 |unknown02| 2013-04-17 | | 6666 |unknown02| 2013-04-17 | +------+---------+------------+ 

now need write sql let result this(let channel's value column)

+----------+---------+---------+---------+ | sys_date |unknown01|unknown02|unknown03| +----------+---------+---------+---------+ |2013-04-15|    1    |    1    |    1    | |2013-04-16|    1    |    0    |    0    | |2013-04-17|    0    |    2    |    0    | +----------+---------+---------+---------+ 

i have no idea sql,so need ,thanks

you can use case that

select sys_date ,sum(case channel when 'unknown01' 1 else 0 end) unknown01 ,sum(case channel when 'unknown02' 1 else 0 end) unknown02 ,sum(case channel when 'unknown03' 1 else 0 end) unknown03 table1 group sys_date 

see sqlfiddle

if don't know number of channels can use dynamic query:

set @sql = null; select   group_concat(distinct     concat(       'sum(case when channel = ''',       channel,       ''' 1 else 0 end) ',       channel     )   ) @sql table1;   set @sql = concat('select sys_date, ', @sql, '                   table1                    group sys_date');  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

see sqlfiddle