SQL sub-consultations with counter

Question

I have a table like this:

++++++++++++++++++++++++++++++++++++++++
+ Nome  | Cargo      | Estado          +
+ ++++++++++++++++++++++++++++++++++++++
+ Joao  | Estagiário | RJ              +
+ Maria | Analista   | RJ              +
+ Thiago| Gerente    | SP              +
+ Pedro | Analista   | SP              +
+ Joana | Estagiário | MG              +
++++++++++++++++++++++++++++++++++++++++

And I would like to select in SQL to get a result like this:

+++++++++++++++++++++++++++++++++++++++++++++++++
+ Estado| Estagiário    | Analista  | Gerente   +
+ +++++++++++++++++++++++++++++++++++++++++++++++
+ MG    | 2             | 3         | 1         +
+ RJ    | 1             | 2         | 1         +
+ SP    | 1             | 2         | 3         +
+++++++++++++++++++++++++++++++++++++++++++++++++

I used the following query:

select count distinct Cargo
from tabela1
where Cargo in ('Analista')
group by Estado

But it didn’t work out so well.

Answer 1

This consultation should work:

SELECT ESTADO,
  COUNT(CASE WHEN CARGO = "ESTAGIARIO" THEN 1 END) estag,
  COUNT(CASE WHEN CARGO = "ANALISTA" THEN 1 END) analista,
  COUNT(CASE WHEN CARGO = "GERENTE" THEN 1 END) gerente
FROM suaTabela
GROUP BY ESTADO;

Answer 2

can hold a pivot.:

SELECT Estado, [Estagiário], [Analista], [Gerente]
FROM (
    SELECT 
        Estado, 
        Cargo 
    FROM @tabela
) AS t
PIVOT(
    COUNT(Cargo) 
    FOR Cargo IN ([Estagiário], [Analista], [Gerente])
) AS p