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 OBJECT_ID('tempdb..#Data') is not null
	drop table #Data

create table #Data
(
	MyName varchar(20),
	Age int,
	Salary int,
	Department varchar(20)
)

insert #Data
select 'SHARATH', 24, 21000, 'MARKETING' union all
select 'RATHAN', 35, 21000, 'MARKETING' union all
select 'RATS', 28, 21000, 'IT' union all
select 'DARSHAN', 23, 20000, 'IT' union all
select 'LINI', 25, 25000, 'IT' union all
select 'SHAN', 23, 20000, 'MARKETING' union all
select 'SUDARSHAN', 22, 20000, 'IT'

select * from #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