--1
select 
(select count(1) from tb where State = 1) [1],
(select count(1) from tb where State = 2) [2],
(select count(1) from tb where State = 3) [3],
(select count(1) from tb where State = 4) [4]


--2
select 
  sum(case State when 1 then 1 else 0 end) [1],
  sum(case State when 2 then 1 else 0 end) [2],
  sum(case State when 3 then 1 else 0 end) [3],
  sum(case State when 4 then 1 else 0 end) [4]
from tb

------解决方案--------------------
SQL code

select distinct state,count(1) over (partition by state) from tab