CREATE TABLE temp
(
    estadoId char(02),
    valor int
)

INSERT INTO temp VALUES ('SP', 1)
INSERT INTO temp VALUES ('SP', 2)
INSERT INTO temp VALUES ('SP', 3)
INSERT INTO temp VALUES ('RJ', 1)
INSERT INTO temp VALUES ('RJ', 2)
INSERT INTO temp VALUES ('XX', 1)
INSERT INTO temp VALUES ('XX', 3)
INSERT INTO temp VALUES ('XX', 3)
INSERT INTO temp VALUES ('XX', 3)
INSERT INTO temp VALUES ('XX', 3)

SELECT estadoID, COUNT(1)
  FROM a_sua_tabela
 GROUP BY estadoID
 ORDER BY estadoID

estadoID       NumeroRegistos
RJ             2
SP             3
XX             5

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT',' + QUOTENAME(c.estadoId)
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT ' + @cols + ' FROM
            (
                SELECT estadoId, valor
                  FROM temp
           ) x
            PIVOT
            (
                 COUNT(valor)
                 FOR estadoID IN(' + @cols + ')
            ) p '

EXECUTE(@query)

RJ  SP  XX
2   3   5