Split one SQL column into multiple based on condition

Question

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

Is this real data/desire? Or this is just a way to simplify more complex problem? — gotqn, Jan 10 '23 at 14:07
@gotqn yes, it's simplified description, but the goal is to split one column into two by condition, without any additional requirements/columns. I got two sets of data with same size and need to split them accordingly. Please let me know if I'm missing something important — pookeeshtron, Jan 10 '23 at 14:16

Tim Biegeleisen · Answer 1 · 2023-01-10T14:37:37.760

0

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.

edited Jan 10 '23 at 14:37

answered Jan 10 '23 at 14:10

Tim Biegeleisen

502,043
27
286
360

Wouldn't that lead to 1 and 3 in one column and 2 and 4 in the other in the example given by the OP? – Jens Schauder Jan 10 '23 at 14:23
@JensSchauder No, and you read my answer wrongly. See the demo here: https://dbfiddle.uk/MXSXXF6Q – Tim Biegeleisen Jan 10 '23 at 14:38
Ok, I get it now. I kind of missed the `MAX` – Jens Schauder Jan 10 '23 at 14:49

Jens Schauder · Answer 2 · 2023-01-10T15:04:28.620

0

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.

edited Jan 10 '23 at 15:04

answered Jan 10 '23 at 14:22

Jens Schauder

77,657
34
181
348

You need `row_number` rather than `rownumber`. – Tim Biegeleisen Jan 10 '23 at 14:52
@TimBiegeleisen Correct. Thanks for noting. I fixed it. – Jens Schauder Jan 10 '23 at 15:01

score 0 · Answer 3 · answered Jan 10 '23 at 14:35

0

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

answered Jan 10 '23 at 14:35

SelVazi

10,028
2
13
29

score 0 · Answer 4 · answered Jan 10 '23 at 15:23

Generally for a table of N rows

WITH yourTable AS (
    SELECT 1 AS id, 'one' AS title UNION ALL
    SELECT 2, 'two' UNION ALL
    SELECT 3, 'three' UNION ALL
    SELECT 4, 'four' UNION ALL
    SELECT 5, 'five' UNION ALL
    SELECT 6, 'six' UNION ALL
    SELECT 7, 'seven'
),
cte AS (
    SELECT *, 
      ROW_NUMBER() OVER(ORDER BY id) - 1 AS rn,
      count(*) over() cnt
    FROM yourTable
)
SELECT 
    max(CASE WHEN rn < cnt / 2 THEN id END) AS id1,
    max(CASE WHEN rn >=cnt / 2 THEN id END) AS id2
FROM cte
GROUP BY rn % round(cnt/2) 
ORDER BY 1;

Returns

id1 id2
1   5
2   6
3   7
4   null

Split one SQL column into multiple based on condition

4 Answers4