MS Sql Server / using row_number()over & reversal of result set.

gistfile1.txt

/*using row_number()over*/
select Id_Otdel,
       firstName,
       row_number()over(partition by Id_Otdel order by firstName) rn
from d_Sotr 
where Id_Otdel in (11, 12, 21)

/*with reversal of result set*/
 select    max(case when Id_Otdel=11
                   then firstName else null end) as analysts,
          max(case when Id_Otdel=12
                   then firstName else null end) as mgrs,
          max(case when Id_Otdel=21
                   then firstName else null end) as prez
    from (
  select Id_Otdel,
         firstName,
         row_number()over(partition by Id_Otdel order by firstName) rn
    from d_Sotr 
   where Id_Otdel in (11, 12, 21)
         ) x 
   group by rn