Distribute data into two columns

Question

I am trying to find out the best-optimized way of splitting alternative rows of a column into two column. Let me explain this by an example

I have the following data

Resultset I want

in the above example, the alternative row is shifted to a new column.I have come up with a solution but it is not optimized if I have millions of records.

My Solution (Not optimized)

;WITH RecCtea
AS
(
SELECT *, ROW_NUMBER()  OVER (ORDER BY (select 1)) rowid FROM tabA ta  
)
SELECT a.cola,b.cola FROM (
(SELECT * FROM RecCtea rc WHERE rc.rowid%2 = 0) a JOIN 
 (SELECT * from RecCtea rc2 where rc2.rowid%2 != 0) b
 on a.rowid = b.rowid+1
 )

SQLfiddle is not working so here is the schema

CREATE TABLE tabA (
cola int 
);


INSERT tabA
(
    cola
)
VALUES
(100),(-100),(200),(-250),(300),(-350),(-400),(NULL)

Answer 1

Try this:

SELECT [0] AS col1, [1] AS col2
FROM 
(
  SELECT cola, 
         (ROW_NUMBER() OVER (ORDER BY (select 1)) + 1) / 2 AS rn,
         ROW_NUMBER() OVER (ORDER BY (select 1)) % 2 rowid 
  FROM tabA 
) AS src
PIVOT (
   MAX(cola) FOR rowid IN ([0],[1])) AS pvt

Output:

col1    col2
------+------
-100  | 100
-250  | 200
-350  | 300
NULL  | -400