Restructure table by dividing columns into rows

Question

Restructure table by dividing columns into rows

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I have a table in the following structure:

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

Among other non-relevant columns

I am restructuring the bank, and need to transform into a structure like this:

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

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

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

mysql

asked by anonymous 09.08.2018 / 17:06

2 answers

0

Do the values in each column need to be in sequence? If not, you can do something like

INSERT INTO TabelaNova VALUES 
(SELECT campo1 FROM TabelaAntiga WHERE campo1 NOT NULL),
(SELECT campo2 FROM TabelaAntiga WHERE campo2 NOT NULL),
(SELECT campo3 FROM TabelaAntiga WHERE campo3 NOT NULL),
(SELECT campo4 FROM TabelaAntiga WHERE campo4 NOT NULL);

09.08.2018 / 17:35

File with strange characters How do I cover this connection and code with the DB for PDO with utf-8 charset? [duplicate]

score 2 · Accepted Answer

This would be an operation called UNPIVOT. To do this in MySQL, use the UNION ALL , like this:

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 in SQLFiddle .

To insert this data, Considering duplicate values , in another table, do the following:

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 in SQLFiddle . You can see that the original number of resgitros is 100 and that after the insert were 396. The difference is the nulls that were removed

Note

A query with UNION will delete 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 only get three A, B, and C records

To remove this behavior use UNION ALL select 'A' field union all select 'A' field union all select 'B' field union all select 'C' field

This way you get the 4 registers: A, A, B, C; as can be seen in this fiddle