SQL Server Join Question

MEG_AGI 

Please use below query

Drop TABLE IF EXISTS #temp
Go
Drop TABLE IF EXISTS #tempfinal
GO

Create table #temp
(
State varchar(10),
County varchar(10),
Rep int,
Amount varchar(100)
)
insert into #temp
select '01','001','100','$300.00' union
select '01','001','999','$22.50' union
select '01','002','300','$750.00' union
select '01','002','999','$12.75' union
select '02','020','400',' $450.00' union
select '02','020','999',' $57.00' union
select '02','030','111',' $645.00'

Alter table #temp add [OtherRep] int,[Other Amount] varchar(200)

;with CTE as (
select *,RANK()over(partition by State,County order by state,County,Rep) as Rnk
from #temp)

select C1.State,C1.County,C1.Rep,C1.Amount,C2.[Rep][other Rep],C2.[Amount] as [Other Amount]
from CTE as C1
inner join CTE as C2 on C2.State=C1.State and C2.county=C1.County AND C2.Rnk=2
where C1.Rnk=1