• with it as (select row_number() over (order by name) as row_num, name, department from #test where department = 'IT')

    ,marketing as (select row_number() over (order by name) as row_num, name, department from #test where department = 'MARKETING')

    select i.name as NameIt,m.name as NameMarketing from it i full outer join marketing m on i.row_num = m.row_num

    If you have more departments you can try to generate a dynamic script based on what i gave you.

    There is always something new to learn.
    My personal SQL Blog[/url]