MYSQL纵表转横表

一、表结构 

（1）import_genedata

（2）gene_info

二、存储过程编写

DROP PROCEDURE GeneInfoDeal;
DROP PROCEDURE GeneInfoUniprotSelect;

-- 每个不重复uniprot的处理
CREATE PROCEDURE GeneInfoDeal(
	uniprot VARCHAR(255)
)
BEGIN
	DECLARE Gene_Name VARCHAR(255);
	DECLARE NCBI_TaxID VARCHAR(255);
	DECLARE RefSeq VARCHAR(255);
	DECLARE MINT VARCHAR(255);
	DECLARE GeneID VARCHAR(255);
	DECLARE Gene_Synonym VARCHAR(255);
	
	-- DECLARE uniprot VARCHAR(255);
	DECLARE mykey VARCHAR(255);
	DECLARE myvalue VARCHAR(255);
	declare flag int default true;

		DECLARE set_values CURSOR FOR SELECT ig.`index`,ig.`xulie` FROM import_genedata_copy1 ig WHERE ig.uniprot = uniprot;
		
		declare continue handler for not found set flag = false;
		
		OPEN set_values;
		REPEAT
		
			FETCH set_values INTO mykey,myvalue;
			
			if mykey = 'Gene_Name'
				then set Gene_Name = myvalue;
			elseif mykey = 'NCBI_TaxID'
				then set NCBI_TaxID = myvalue;
			elseif (mykey = 'RefSeq' or mykey = 'RefSeq_NT')
				then set RefSeq = CONCAT_WS(',',RefSeq,myvalue);
			elseif mykey = 'MINT'
				then set MINT = uniprot;
			elseif mykey = 'GeneID'
				then set GeneID = myvalue;
			elseif mykey = 'Gene_Synonym'
				then set Gene_Synonym = myvalue;
			end if;
			
		UNTIL flag = false END REPEAT;
		CLOSE set_values;
		
		INSERT INTO `gene_info` (`Gene_Name`,`NCBI_TaxID`,`RefSeq`,`MINT`,`GeneID`,`Gene_Synonym`) VALUES
		(Gene_Name,NCBI_TaxID,RefSeq,MINT,GeneID,Gene_Synonym);

		
END;

-- 查询不充分uniprot
CREATE PROCEDURE GeneInfoUniprotSelect()
BEGIN
	
	DECLARE uniprot VARCHAR(255);
	DECLARE flag INT DEFAULT TRUE;
	
	-- 游标
	DECLARE set_uniprot CURSOR FOR SELECT DISTINCT ig.`uniprot` FROM import_genedata_copy1 ig;
	
	declare continue handler for not found set flag = false;
	
	OPEN set_uniprot;
	REPEAT
	
		FETCH set_uniprot INTO uniprot;
		
		CALL GeneInfoDeal(uniprot);
		
 	UNTIL flag = false END REPEAT;
	CLOSE set_uniprot;
		
END;

CALL GeneInfoDeal('P48347');
CALL GeneInfoUniprotSelect();