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

Answer 1

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