Split one SQL column into multiple based on condition-CodePudding

I need to split one column in two based on some condition. Here is an example of table:

id | title
----------
1  | one
2  | two
3  | three
4  | four

So, I'd like to have a view with two columns like id1 and id2, first one will contains ids that are lower than 3, second one - the rest ids, results should be consecutive. Expected result is

id1 | id2
----------
1   | 3
2   | 4

CodePudding user response：

This is compatible for mysql 5 :

select 
  MAX(case when id <= 2 then id END) AS id1,
  MAX(case when id > 2 then id END) AS id2
from (
  select 
  id,
  IF(id%2, 1, 0) as gp
  from TABLE1
) as s
GROUP BY gp
ORDER BY id1,id2

CodePudding user response：

Without seeing more data, the exact requirement is not entirely clear. Here is one interpretation using ROW_NUMBER() with pivoting logic:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) - 1 AS rn
    FROM yourTable
)

SELECT
    MAX(CASE WHEN FLOOR(rn / 2) = 0 THEN id END) AS id1,
    MAX(CASE WHEN FLOOR(rn / 2) = 1 THEN id END) AS id2
FROM cte
GROUP BY rn % 2
ORDER BY 1;

Here is a working demo.

CodePudding user response：

This should do the trick.

select small.id as id1, big.id as id2
from(
  select id, title, 
    row_number() over (order by id) rn
  from demo
  where id < 3
) small
join (
  select id, title, 
    row_number() over (order by id) rn 
  from demo
  where id >=3
) big
on small.rn = big.rn

For simplicity I used a JOIN, if you can't guarantee both parts have the same number of rows, you might have to use a LEFT, RIGHT or FULL OUTER JOIN

I put a working example on dbfiddle.

This approach compared to variants using GROUP BY is that all other columns can easily be used in the final query.