Create columns with the frequency of a code per ID

I have to perform a query where I can count the number of distinct codes per Id.

   |Id  | Code
   ------------
   | 1  |   C   
   | 1  |   I   
   | 2  |   I   
   | 2  |   C   
   | 2  |   D   
   | 2  |   D   
   | 3  |   C
   | 3  |   I   
   | 3  |   D 
   | 4  |   I   
   | 4  |   C
   | 4  |   C   

The output should be something like:

   |Id  | Count | #Code C | #Code I | #Code D
   -------------------------------------------
   | 1  | 2     |    1    |    1    |   0  
   | 2  | 3     |    1    |    0    |   2  
   | 3  | 3     |    1    |    1    |   1  
   | 4  | 2     |    2    |    1    |   0  

Can you give me some advise on this?

Answer

Use conditional aggregation:

select id, count(*) as cnt,
       sum(case when code = 'C' then 1 else 0 end) as code_c,
       sum(case when code = 'I' then 1 else 0 end) as code_i,
       sum(case when code = 'D' then 1 else 0 end) as code_d
from t
group by id;