CODEWITHSUNDEEP
mysqlunpivot
Rossella
Rossella

Reputation: 189

Merging multiple columns into one creating one row for each column in mysql

I have a mysql table with the following columns

SNP
Probe
Genotype_00
Mean_00
Std_00
Genotype_01
Mean_01
Std_01
Genotype_11
Mean_11
Std_11

I would like the table to have the following columns:

SNP
Probe
Genotype
Mean 
Std

where for each pair (SNP,Probe) I have three rows, one corresponding to each genotype.

For example the row

rs1 probe1 AA 5.6 0.3 AG 4.3 0.2 GG 3.4 0.1

should be transformed into

rs1 probe1 AA 5.6 0.3
rs1 probe1 AG 4.3 0.2
rs1 probe1 GG 3.4 0.1

Can anyone help me finding a way to do this? Thanks a lot in advance, any suggestion will be greatly appreciated

Rossella

Upvotes: 0

Views: 519

Answers (2)

Gang Su
Gang Su

Reputation: 1207

You may simply create a new table with SNP Probe Genotype Mean Std, then use 

insert into NEWTABLE
select SNP, Probe, Genotype_00, Mean_00, Std_00
from old table

Then change 00 to 01 and 11, run the queries again.

However, as the snp pairs won't be next to each other, you may want to create indices.

Upvotes: 0

Jonas T
Jonas T

Reputation: 3077

Create Table with follow columns.

SNP
Probe
Genotype
Mean 
Std

Then execute these SQL statements one by one

INSERT INTO new_table
SELECT SNP, Probe, Genotype_00, Mean_00, Std_00
FROM old_table

INSERT INTO new_table
SELECT SNP, Probe, Genotype_01, Mean_00, Std_01
FROM old_table


INSERT INTO new_table
SELECT SNP, Probe, Genotype_11, Mean_00, Std_11
FROM old_table

Upvotes: 1

Related Questions