mysql moving 2 columns to one reordening the data where not 0

Question

Could this table be re-arranged into one table (using a view )

+----+------+--------+
| id | item1| item2  |
+----+------+--------+
|  1 |    A |  B     |
|  2 |    0 |  B     |
|  3 |    A |  0     |
|  4 |    0 |  0     |
+----+------+--------+

Moving the data to this arrangement:

+----+------+
| id |items |
+----+------+
|  1 |    A |
|  2 |    B |
|  3 |    B |
|  4 |    A |
+----+------+

Answer 1

I think that to unpivot the data, while ignoring 0 values.

Consider:

select 
    row_number() over(order by t.id, t.seq) id,
    t.item
from (
    select id, 1 seq, item1 item from mytable where item1 <> '0'
    union all select id, 2, item2 from mytable where item2 <> '0'
) t
order by t.id, t.seq