Show row values as columns to display in a gridview

Question

I have a Mysql database with a table having two columns Department and deals as:

Department | deals 
HR         | A1  
Sales      | A2 
Revenue    | A1 
HR         | A3  

I want to display the data in asp.net gridview as:

         A1| A2 | A3 
HR       1    0    1
Sales    0    1    0
Revenue  1    0    0

How can I do the same as the values under deals are not known or fixed. I am using below but its not showing desired output

SELECT GROUP_CONCAT(CONVERT(deals,char(10)))
FROM Table1
group by Department

Answer 1

Try this:

SELECT Department, 
       SUM(deals='A1') AS A1, 
       SUM(deals='A2') AS A2, 
       SUM(deals='A3') AS A3
FROM Table1
GROUP BY Department

For Dynamic Deals:

SELECT GROUP_CONCAT(DISTINCT CONCAT("SUM(deals='", deals, "') AS '", deals, "'")) INTO @temp
FROM Table1;

SET @sql = CONCAT('SELECT Department, ', @temp, 'FROM Table1 GROUP BY Department');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

OR

SELECT Department, 
       SUM(IF(deals='A1', 1, 0)) AS A1, 
       SUM(IF(deals='A2', 1, 0)) AS A2, 
       SUM(IF(deals='A3', 1, 0)) AS A3
FROM Table1
GROUP BY Department;

For Dynamic Deals:

SELECT GROUP_CONCAT(DISTINCT CONCAT("SUM(IF(deals='", deals, "', 1, 0)) AS '", deals, "'")) INTO @temp
FROM Table1;

SET @sql = CONCAT('SELECT Department, ', @temp, 'FROM Table1 GROUP BY Department');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;