SQL - Split a column into two columns in Mysql

Question

I have this table. Considering the id starts from 0.

Table 1

ID     Letter
1        A
2        B
3        C
4        D
6        E

I need following output

Col1     Col2
NULL      A
B         C
D         NULL
E         NULL

I tried using union with id, id - 1 and id + 1, but I couldn't figure out how to get letter based on ids, also tried even odd logic but nothing worked.

Any help is appreciated.

Thank you

Can you please format the tables so that the rows and columns are clearer? — Cade Roux
– Cade Roux, Commented Oct 11, 2018 at 18:15

The Impaler · Accepted Answer · 2018-10-11 19:29:29Z

3

You didn't post the database engine, so I'll assume PostgreSQL where the modulus operand is %.

The query should be:

select o.letter, e.letter
  from (
    select id, letter, id as base from my_table where id % 2 = 0
  ) o full outer join (
    select id, letter, (id - 1) as base from my_table where id % 2 <> 0
  ) e on e.base = o.base
  order by coalesce(o.base, e.base)

Please take the following option with a grain of salt since I don't have a way of testing it in MySQL 5.6. In the absence of a full outer join, you can perform two outer joins, and then you can union them, as in:

select * from (
  select o.base, o.letter, e.letter
    from (
      select id, letter, id as base from my_table where id % 2 = 0
    ) o left join (
      select id, letter, (id - 1) as base from my_table where id % 2 <> 0
    ) e on e.base = o.base
  union
  select e.base, o.letter, e.letter
    from (
      select id, letter, id as base from my_table where id % 2 = 0
    ) o right join (
      select id, letter, (id - 1) as base from my_table where id % 2 <> 0
    ) e on e.base = o.base
) x
order by base

edited Oct 11, 2018 at 19:29

answered Oct 11, 2018 at 18:20

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Zack Over a year ago

Well, that certainly gets the job done, despite scant details from OP. Well done. :)

Rupz Over a year ago

Thank you for quick response with so less details. The logic works fine but it's MySQL and full outer join wouldn't work.

The Impaler Over a year ago

Which version of MySQL? 5.x or 8.x?

Rupz Over a year ago

MySQL 5.6. Also I tried using left and right join then union but don't know how to proceed further

The Impaler Over a year ago

Please take a look at the second SQL. I hope it works, but it becomes increasingly uglier.

|

Gordon Linoff · Accepted Answer · 2018-10-11 21:59:30Z

3

Just use conditional aggregation:

select max(case when id % 2 = 0 then letter end) as col1,
       max(case when id % 2 = 1 then letter end) as col2
from t
group by floor(id / 2);

If you prefer, you can use mod() instead of %. MySQL supports both.

edited Oct 11, 2018 at 21:59

answered Oct 11, 2018 at 19:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

dossy Over a year ago

Going to suggest an edit to correct a minor typo: floor(i / 2) should be floor(id / 2) if I'm not mistaken?

dossy Over a year ago

I have to say, using max() and group by floor(n / 2) is really clever - I'd never seen that used before. How did you come up with that? Had you already seen that somewhere else?

Gordon Linoff Over a year ago

@dossy . . . No doubt, I've seen similar problems. The solution is more obvious to me than a join based solution. The problem "looks" like conditional aggregation to me.

Collectives™ on Stack Overflow

SQL - Split a column into two columns in Mysql

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related