Count the number of appearances of char given a 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

If the codes are only to the provided ones, the following query can provide the desired result.

    select 
      pvt.Id,
      codes.total As [Count],
      COALESCE(C, 0) AS [#Code C],
      COALESCE(I, 0) AS [#Code I],
      COALESCE(D, 0) AS [#Code D]
      from 
      ( select Id, Code,  Count(code) cnt
        from t
        Group by Id, Code) s
      PIVOT(MAX(cnt) FOR Code IN ([C], [I], [D])) pvt
      join (select Id, count(distinct Code) total from t group by Id) codes on pvt.Id = codes.Id ;

Note: as I can see from sample input data, code ‘I’ is found in all of Ids. Its count is zero for Id = 3 in the expected output (in the question).

Here is the correct output:

DB Fiddle