Read the values ​​that exist in another table and rotate the result

Question

I want to count values ​​from a table Chat:

ID    REASON_ID    DEPARTMENT_ID    
 1      46           1
 2      46           1
 3      50           1
 4      50           2
 5      100          1 
 6      100          2

They exist in the table Reason:

ID    REASON_NAME
46    Reason1
50    Reason2
100   Reason3

Where DEPARTMENT_ID=1, and I want to get the result as follows:

ID46  ID50  ID100
 2      1     1  

How can i do this?

Answer 1

A dynamic SQL solution is better, but if you want another option:

SELECT SUM(I46) ID46,
SUM(I50) ID50,
SUM(I100) ID100
FROM
(SELECT 
COUNT(CASE WHEN reason_id = 46 THEN 1 END) I46,
COUNT(CASE WHEN reason_id = 50 THEN 1 END) I50,
COUNT(CASE WHEN reason_id = 100 THEN 1 END) I100
FROM chat
WHERE department_id = 1
GROUP BY reason_id) q1;

Answer 2

You can try the following:

set @sql = null;
select group_concat( distinct
  concat( ' sum(r.id= ', r.id,') as ID', r.id )
) into @sql
from Chat c
join Reason r on c.reason_id = r.id 
where c.department_id = 1;

set @sql = concat('select ',@sql, ' 
from Chat c
join Reason r on c.reason_id = r.id 
where c.department_id = 1');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

SQLFiddle