Count occurrences in SQL

I have a table with the following data

| id | type |
-------------
| 1  | a    |
| 1  | b    |
| 1  | a    |
| 2  | a    |
| 2  | b    |
| 3  | a    |

And I would like to have for each ID how many occurrences of each type there are

| id | a | b |
| 1  | 2 | 1 |
| 2  | 1 | 1 |
| 3  | 1 | 0 |

Is there a way in SQL (something like a pivot table)

Answer

I would recommend conditional aggregation. This is a cross-database solution that is more flexible than vendor-specific solutions (and at least as efficient):

select
    id,
    sum(case when type = 'a' then 1 else 0 end) a,
    sum(case when type = 'b' then 1 else 0 end) b
from mytable
group by id