-3

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 |
+----+------+
GMB
  • 216,147
  • 25
  • 84
  • 135

1 Answers1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • thanks, but id, t.item at the end of the select gives unexpected tokens and alias already used, when I try to run this –  Mar 01 '20 at 21:54
  • @100IT: which version of MySQL are you running? – GMB Mar 01 '20 at 22:04
  • Mysql version 5.7.26 –  Mar 01 '20 at 22:40
  • 1
    `row_number()` requires MySQL 8.0. I am unsure that you really want a new sequence id in the result. In earlier versions, I would just do: `select id, seq, item from (....) t order by id, seq` – GMB Mar 01 '20 at 23:01
  • I realise I'm pushing the edge of what is possible here, (& what is logic to do) hence the negative responses, I guess. I did solve this using javascript on the client side, just wanted to know what is possible. Thanks for your help! –  Mar 02 '20 at 09:11