T-SQL Pivot table not using IN and values from another column in the pivot data based on MIN() & MAX

The window function row_number() over() in concert with a conditional aggregation should do the trick

Select [Job Description]
      ,[Most]  = max( case when RN1 = 1 then worker end)
      ,[Least] = max( case when RN2 = 1 then worker end)
 From (
        Select * 
              ,RN1 = row_number() over (partition by [Job Description] order by [Years of Experience] desc)
              ,RN2 = row_number() over (partition by [Job Description] order by [Years of Experience] asc)
         from YourTable
      ) A
 Group By [Job Description]

Results

Job Description  Most   Least
Doctor           4004   6006
Lawyer           3003   1001
Scientist        7007   8008