Question 21334 in Mysql Question posted in
The official MySQL documentation can be found here.

Split one SQL column into multiple based on condition – Mysql

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
Tags:

4

Answers


  1. 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.

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
  3. 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
    Login or Signup to reply.
  4. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search