Reimundo Heluani Reimundo Heluani - 2 months ago 8
MySQL Question

SQL : splitting a row in two rows

I have a table that looks like this

| ID | item1 | item 2 |
| 1 | A1 | B1 |
| 2 | A2 | B2 |


And I want to get something like this:

| ID | item |
| 1 | A1 |
| 1 | B1 |
| 2 | A2 |
| 2 | B2 |


In some sense it is the inverse to this SO question.

Answer

Use UNION ALL:

SELECT ID, item1 AS item
FROM mytable

UNION ALL

SELECT ID, item2 AS item
FROM mytable
ORDER BY ID, item
Comments