Restructure table by dividing columns into rows

Question

I have a table in the following structure:

| campo1 | campo2 | campo3 | campo4 |
| --------------------------------- |
| valor1 | valor2 | valor3 | valor4 |
| valor5 |  NULL  |  NULL  | valor8 |

Among other columns not relevant

I’m restructuring the bank, and I need to turn it into a structure like this:

| campo  |
| ------ |
| valor1 |
| valor2 |
| valor3 |
| valor4 |
| valorN |

Is it possible to do this only with SQL? How?

They are exact 4 columns that I need to turn into 4 rows or less (some fields are null). The primary key is not being referenced anywhere else, not being imported (it already deletes the field and I will insert it again when the table is restructured)

Answer 1

That would be an operation called UNPIVOT. To do this in Mysql, use UNION ALL, thus:

select campo1 from mytable where campo1 is not null
union ALL
select campo2 from mytable where campo2 is not null
union ALL
select campo3 from mytable where campo3 is not null
union ALL
select campo4 from mytable where campo4 is not null

See working on Sqlfiddle.

To enter this data, considering double values, in another table, do so:

Insert into `NovaTabela` (campo) Select campo from (

select campo1 as campo from mytable where campo1 is not null
union all
select campo2  as campo from mytable where campo2 is not null
union all
select campo3  as campo from mytable where campo3 is not null
union all
select campo4  as campo from mytable where campo4 is not null) T; 

See working on Sqlfiddle. You can see that the number of resgitros original is 100 and that after the Insert were 396. The difference are the nulls that were removed

Note

A query with UNION will eliminate duplicate values.
If you do

select 'A' as campo
union
select 'A'  as campo
union
select 'B'  as campo
union
select 'C'  as campo

You will get only three records A, B and C

To remove this behavior use UNION ALL select 'A' as fields Union all select 'A' fields Union all select 'B' fields Union all select 'C' fields

In this way Voce obtains the 4 records: A, A, B, C; as can be seen in this fiddle