Q:

Pivoting in MySQL

I have a table in MySql tbl_Analysis with following structure
uid | device_type
 1      Desktop
 2      Desktop
 3      Mobile
 4      Mobile
 5      Laptop
 6      Desktop

Now i need to get no. of users count group by device_type
I write the following query for this
select count(device_type) as count, 
device_type as device 
from tbl_Analysis 
group by device_type;

Following is result
count | device
 3       Desktop
 2       Mobile
 1       Laptop

Now I want these result to be pivot. In Ms-Sql there is built in functionality available but I could not find any way of doing this in MySQL.
My desired result is: 
Desktop | Mobile | Laptop
3          2        1

A:

You can use a case expression to generate the pivot view.
Query
select 
count(case when device_type = 'Desktop' then device_type end) as Desktop,
count(case when device_type = 'Mobile' then device_type end) as Mobile,
count(case when device_type = 'Laptop' then device_type end) as Laptop
from tb_analysis;

SQL Fiddle
Another way to achieve this by dynamic sql.
Query
set @query = null;
select
group_concat(distinct
    concat(
      'count(case when device_type = ''',
      device_type, ''' then device_type end) as ' ,device_type
    )
  ) into @query
from tb_analysis ;

set @query = concat('select ', @query, ' from tb_analysis
');

prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

SQL Fiddle

