pivot table - Pivoting in MySQL -

i have table in mysql tbl_analysis following structure

uid | device_type  1      desktop  2      desktop  3      mobile  4      mobile  5      laptop  6      desktop 

now need no. of users count group device_type

i write following query this

select count(device_type) count,  device_type device  tbl_analysis  group device_type; 

following result

count | device  3       desktop  2       mobile  1       laptop 

now want these result pivot. in ms-sql there built in functionality available not find way of doing in mysql.

my desired result is:

desktop | mobile | laptop 3          2        1 

you can use case expression generate pivot view.

query

select  count(case when device_type = 'desktop' device_type end) desktop, count(case when device_type = 'mobile' device_type end) mobile, count(case when device_type = 'laptop' device_type end) laptop tb_analysis; 

sql fiddle

another way achieve dynamic sql.

query

set @query = null; select group_concat(distinct     concat(       'count(case when device_type = ''',       device_type, ''' device_type end) ' ,device_type     )   ) @query tb_analysis ;  set @query = concat('select ', @query, ' tb_analysis ');  prepare stmt @query; execute stmt; deallocate prepare stmt; 

sql fiddle