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

Question

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

Jonas T · Accepted Answer · 2012-05-16 04:13:53Z

1

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

answered May 16, 2012 at 4:13

Jonas T

3,0954 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bernhard Barker · Accepted Answer · 2013-02-13 12:04:54Z

0

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.

edited Feb 13, 2013 at 12:04

Bernhard Barker

55.7k14 gold badges111 silver badges143 bronze badges

answered May 16, 2012 at 4:13

Gang Su

1,22711 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related