So that you don't have to worry about hard coding years in your script, you could do :

WITH cte AS (

SELECT DISTINCT Sales_Rep, PYear, Number_of_Employees FROM mytable

)

SELECTcoalesce(py.sales_rep, cy.sales_rep) as salesRep,

py.numberOfEmployees as priorYear,

cy.numberOfEmployees as currentYear

FROM

(

selectsales_rep, pyear ,

sum(Number_of_Employees) as [numberOfEmployees]

fromcte

wherepyear = (select max(pyear) from myTable)

groupby sales_Rep,pyear

) as cy

Full outer join

(

selectsales_rep, pyear,

sum(Number_of_Employees) as [numberOfEmployees]

fromcte

wherepyear = (select max(pyear) from myTable where pyear<>(select max(pyear) from myTable))

groupby sales_Rep,pyear

) as py

ON py.sales_rep = cy.sales_rep

However I like more the style of just having the years as your column headers , if you later wanted to look back many years. In this latter case, the solution would be better off as a pivot report (sales reps on row header and years as the column headers).