By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table t (
Id int not null,
Code Char(1) not null
);
Insert into t
values
(1,'C'),
(1,'I'),
(2,'I'),
(2,'C'),
(2,'D'),
(2,'D'),
(3,'C'),
(3,'I'),
(3,'D'),
(4,'I'),
(4,'C'),
(4,'C');
12 rows affected
select
pvt.Id,
codes.total As [Count],
COALESCE(C, 0) AS [#Code C],
COALESCE(I, 0) AS [#CodeI],
COALESCE(D, 0) AS [#CodeD]
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 ;
| Id | Count | #Code C | #CodeI | #CodeD |
|---|---|---|---|---|
| 1 | 2 | 1 | 1 | 0 |
| 2 | 3 | 1 | 1 | 2 |
| 3 | 3 | 1 | 1 | 1 |
| 4 | 2 | 2 | 1 | 0 |