This should work based on the sample data.

;with Marketing as

(

select *, ROW_NUMBER() over (order by MyName) as RowNum

from #Data

where Department = 'Marketing'

)

, IT as

(

select *, ROW_NUMBER() over (order by MyName) as RowNum

from #Data

where Department = 'IT'

)

select m.MyName as Marketing, IT.MyName as IT

from Marketing m

full outer join IT on m.RowNum = IT.RowNum

Let me know if that works for you.

Sorry about complaining about your capitals. Must have been in a bad mood yesterday when I posted that. 😉