CODEWITHSUNDEEP
sqlsql-serversql-order-by
IRONLORD
IRONLORD

Reputation: 163

Order By SQL - Set output as if it were to build two columns

I have this output: 1,2,3,4,5,6,7 but it needs to be like this 1,5,2,6,3,7,4 or for example 1,2,3,4,5,6,7,8 into 1,5,2,6,3,7,4,8

1 2      1 5
3 4  OR  2 6
5 6      3 7
7        4

needs to be like this:

1 2      1 5
3 4   OR 2 6
5 6      3 7
7 8      4 8

UPDATED: The result must need to be one column only

1             1
2             5
3             2
4     -->     6
5             3
6             7
7             4

It's for creating this HTML dynamically: enter image description here

I'm sure there is a solution to this, just don't know the key words to search on the internet and I can't find a logic to do this order by.

EDIT: http://sqlfiddle.com/#!9/57f28d/3

Upvotes: 1

Views: 76

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to count your rows and number them. Then all you need to do is find a math term to group by with. E.g.

select  min(val), nullif(max(val), min(val))
from
(
  select 
    val,
    row_number() over (order by val) as rn,
    count(*) over () as cnt
  from dummy 
) numbered
group by rn % ((cnt+1) / 2)
order by min(val);

Result:

1 | 2
3 | 4
5 | 6
7 | 8

SQL fiddle: http://sqlfiddle.com/#!18/57f28/118

UPDATE: You just want the single values ordered such that you can easily read them in a loop and create an HTML table from it. So use ROW_NUMBER OVER and COUNT OVER in the ORDER BY clause:

select val
from dummy 
order by 
  (row_number() over (order by val) - 1) % ((count(*) over () + 1) / 2),
  row_number() over (order by val);

SQL fiddle: http://sqlfiddle.com/#!18/25a5e/1

This is easily adaptable by the way. If at one time you want to create three instead of two columns for instance, then simply change / 2 to / 3 (SQL fiddle http://sqlfiddle.com/#!18/25a5e/2).

Upvotes: 2

DxTx
DxTx

Reputation: 3357

Try this... (using case)

Solution 01

DECLARE @cnt int = (SELECT count(*) from Dummy)

SELECT 
case when VAL <= @cnt/2 then VAL end col1,
case when VAL <= @cnt/2 then VAL + @cnt/2 end col2
FROM Dummy
where VAL <= @cnt/2

Output 

+------+------+
| col1 | col2 |
+------+------+
|    1 |    5 |
|    2 |    6 |
|    3 |    7 |
|    4 |    8 |
+------+------+

Online Demo: http://sqlfiddle.com/#!18/57f28/58/0

Solution 02

SELECT
case when (VAL%2) = 1 then VAL end as col1,
case when (VAL%2) = 1 then VAL + 1 end as col2
FROM Dummy
where VAL%2 = 1

Output

+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
+------+------+

Online Demo: http://sqlfiddle.com/#!18/57f28/76/0

  

Updated 01...

Solution 01

DECLARE @cnt INT = (SELECT Count(*) FROM   dummy) 

SELECT CASE 
         WHEN val <= @cnt / 2 THEN CONVERT(NVARCHAR(5), val) + ' ' 
                                   + CONVERT(NVARCHAR(5), val + @cnt/2) 
       END col1 
FROM   dummy 
WHERE  val <= @cnt / 2

Output

+------+
| col1 |
+------+
|  1 5 |
|  2 6 |
|  3 7 |
|  4 8 |
+------+

Online Demo: http://sqlfiddle.com/#!18/57f28/124/0

Solution 02

SELECT CASE 
         WHEN ( val%2 ) = 1 THEN CONVERT(NVARCHAR(5), val) + ' ' 
                                 + CONVERT(NVARCHAR(5), (val + 1)) 
       END AS col1 
FROM   dummy 
WHERE  val%2 = 1

Output

+------+
| col1 |
+------+
|  1 2 |
|  3 4 |
|  5 6 |
|  7 8 |
+------+

Online Demo: http://sqlfiddle.com/#!18/57f28/114/0

  

Updated 02...

SELECT id, 
       CASE 
         WHEN colab = 0 THEN colab2 + (SELECT Count(*) FROM dummy) / 2 ELSE colab2 
       END OutputCol 
FROM   (SELECT id, 
               (id%2) AS colAB, 
               Row_number() OVER (partition BY (id%2) ORDER BY id) AS colAB2 
        FROM   dummy) t1 
ORDER  BY id

Output

+----+-----------+
| id | OutputCol |
+----+-----------+
|  1 |         1 |
|  2 |         5 |
|  3 |         2 |
|  4 |         6 |
|  5 |         3 |
|  6 |         7 |
|  7 |         4 |
|  8 |         8 |
+----+-----------+

Note: You can remove the ID column and get only one column (OutputCol) if you want.

SELECT id, CASE WHEN colab....

Online Demo: http://sqlfiddle.com/#!18/57f28/257/1

Upvotes: 1

SQLChao
SQLChao

Reputation: 7847

Maybe something like this would work. Used ROW_NUMBER() to number each row consecutively. Then self join one row number to the previous row number. Filter only the odd rows.

SQL Fiddle

;WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn
FROM dummy)


SELECT c1.val, c2.val
FROM cte c1
LEFT JOIN cte c2 ON c2.rn - 1  = c1.rn
WHERE c1.rn % 2 = 1

Upvotes: 3

Related Questions