sql server - how to to get first top 6 records indifferent columns T-sql? -

i got situation display first top 6 records. first 3 records in firstcol , next 3 in secondcol. query this:

select top 6 [empname]  [emp ] order [salary] desc 

result:

[empname] ---------------------- sam            pam            oliver         jam kim nixon 

but want result this:

firstcol      secondcol sam           jam pam           kim oliver        nixon 

you can using several windowing functions, kind of ugly result want:

;with data (   -- top 6   select top 6 empname, salary   emp   order salary desc ),  buckets (   -- use ntile split 6 rows 2 buckets   select empname,      nt = ntile(2) over(order salary desc),     salary   data )  select    firstcol = max(case when nt = 1 empname end),   secondcol = max(case when nt = 2 empname end)  (   -- create row number each item in buckets return multiple rows   select empname,     nt,     rn = row_number() over(partition nt order salary desc)   buckets ) d group rn; 

see sql fiddle demo. uses function ntile, takes dataset of 6 rows , splits 2 buckets - 3 rows in bucket 1 , 3 rows in bucket 2. (2) inside ntile used determine number of buckets.

next used row_number() create unique value each row within each bucket, allows return multiple rows each column.