Two-column data distribution

Question

I am trying to find an optimally optimized way to split alternate column rows into two columns. Let me explain this with an example.

I have the following data

Result i want

in the above example, the alternate row is shifted to a new column. I came 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 does not work, 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 the following:

 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 

Answer 2

You can do this with conditional aggregation, CASE EXPRESSION , ROUND and ABS , there is no need for window functions / rotations:

 SELECT MAX(CASE WHEN t.cola < 0 THEN t.cola END) as cola_minus, MAX(CASE WHEN t.cola > 0 THEN t.cola END) as cola_plus FROM (SELECT s.cola,ABS(ROUND(t.cola/100)) as group_number FROM TabA s) t GROUP BY t.group_number 

Each group will receive a new group_number value, which is the absolute result of the cola/100 round. Then he will group together with him and use conditional aggregation to rotate them.

Answer 3

Another way to use CTE:

 ;WITH cte AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rn, cola FROM tabA ) SELECT c2.cola, c1.cola FROM cte c1 LEFT JOIN cte c2 ON c1.rn = c2.rn-1 WHERE c1.rn%2 = 1 AND c2.rn%2 = 0 

Output:

 cola cola -100 100 -250 200 -350 300 NULL -400