mysql存储过程实现行转列

CREATE PROCEDURE columnConverRow (in inTableName varchar(50),in inTableSchema varchar(50),out rsql TEXT)
BEGIN
			
DECLARE columnC int;
DECLARE dateC int;
DECLARE columnName VARCHAR(126);
DECLARE v1 INT DEFAULT 0;
DECLARE v2 INT DEFAULT 0;
set @str = '';
select count(1) into columnC from information_schema.columns where table_name=inTableName and table_schema=inTableSchema;
set @tempTableName = inTableName;
SET @tempSql = CONCAT('SELECT @tempDateC:=count(1) FROM ', @tempTableName);
PREPARE st FROM @tempSql;
EXECUTE st;
DEALLOCATE PREPARE st;
set dateC = @tempDateC;

set @FinalSql = '';
set @ColumnSql = '';
set @RN = 0;
set @fianlQueryColumn = '';
set @rowName = CONCAT('(select (@RN := @RN + 1) ROWNUM,column_name cn from information_schema.columns where table_name="',inTableName,'" and table_schema="',inTableSchema,'") rowName');
WHILE v1 < dateC DO
		set v2 = 0; 
		if v1=0 then set @fianlQueryColumn = CONCAT('cn,cv',v1);
		else set @fianlQueryColumn = CONCAT(@fianlQueryColumn,',','cv',v1);
		end if;
		while v2 < columnC DO
				select COLUMN_NAME into columnName from information_schema.columns where table_name=inTableName and table_schema=inTableSchema limit v2,1;
				if v2 = 0 THEN set @str = CONCAT('(select (@RN := 1) ROWNUM,',columnName,' cv',v1,' from ',inTableName,' limit ',v1,',1) ');
				else set @str = CONCAT(@str,'union all ','(select (@RN := @RN + 1) ROWNUM,',columnName,' from ',inTableName,' limit ',v1,',1) '); 
				END IF;
				set v2 = v2 + 1;
		END WHILE;
		set @ColumnSql = CONCAT(@ColumnSql,' left join (',@str,')',v1,'column on ',v1,'column.ROWNUM=rowName.ROWNUM ');
		SET v1 = v1 + 1;
END WHILE;
set @rowNameSql = CONCAT('select ',@fianlQueryColumn,' from ',@rowName);
set @FinalSql = CONCAT(@rowNameSql,@ColumnSql);
prepare stmt1 from @FinalSql;
execute stmt1;
deallocate prepare stmt1;
set @FinalSql = CONCAT('set @RN = 0;',@rowNameSql,@ColumnSql);
set rsql = @FinalSql;
END;

调用上面创建的存储过程：

CALL columnConverRow('uc_account','user_center',@rsql);

执行存储过程，第一个参数为表名，第二个参数为表空间名，第三个参数为返回执行的行转列sql。

select @rsql;

获取执行的行转列sql。

2.0版本编写中。。。
临时存放
select concat(‘IFNULL(’,replace(GROUP_CONCAT(COLUMN_NAME),’,’,’,“null”),",",IFNULL(’),’,“null”)’) from information_schema.columns where table_name=‘uc_account’ and table_schema=‘user_center’;

select @tempsqlret1:=GROUP_CONCAT(IFNULL(id,“null”),",",IFNULL(tenant_code,“null”),",",IFNULL(app_code_sys,“null”),",",IFNULL(app_code,“null”),",",IFNULL(login_account,“null”),",",IFNULL(mobile,“null”),",",IFNULL(email,“null”),",",IFNULL(from_ip_address,“null”),",",IFNULL(password,“null”),",",IFNULL(password_high,“null”),",",IFNULL(user_type,“null”),",",IFNULL(user_state,“null”),",",IFNULL(apply_way,“null”),",",IFNULL(mobile_activation_sign,“null”),",",IFNULL(email_activation_sign,“null”),",",IFNULL(is_login,“null”),",",IFNULL(cust_id,“null”),",",IFNULL(password_update_time,“null”),",",IFNULL(password_update_time_high,“null”),",",IFNULL(register_time,“null”),",",IFNULL(register_time_high,“null”),",",IFNULL(from_sg_provice,“null”),",",IFNULL(create_time,“null”),",",IFNULL(update_time,“null”),",",IFNULL(update_time_high,“null”),",",IFNULL(address_province,“null”),",",IFNULL(address_city,“null”),",",IFNULL(address_region,“null”),",",IFNULL(nickname,“null”),",",IFNULL(photo,“null”),",",IFNULL(id_card,“null”),",",IFNULL(bank_card_no,“null”),",",IFNULL(real_name,“null”),",",IFNULL(real_type,“null”),",",IFNULL(department_number,“null”),",",IFNULL(department_type,“null”),",",IFNULL(is_admin,“null”),",",IFNULL(post,“null”),",",IFNULL(name,“null”),",",IFNULL(scan_status,“null”),",",IFNULL(is_complement_info,“null”)) from uc_account LIMIT 1;

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(@tempsqlret1,’,’,help_topic_id+1),’,’,-1) AS num
FROM
mysql.help_topic
WHERE
help_topic_id < LENGTH(@tempsqlret1)-LENGTH(REPLACE(@tempsqlret1,’,’,’’))+1;

-----------------------------分割线---------------------------------------------------------------------------------------------

关于MySQL存储过程的参考示例：

CREATE PROCEDURE columnConverRow (in buildRecordId varchar(50),out rsql varchar(50))
BEGIN
DECLARE v1 INT DEFAULT 0;
DECLARE v2 INT DEFAULT 0;
DECLARE v3 INT DEFAULT 0;
DECLARE v4 INT DEFAULT 0;
DECLARE v5 INT DEFAULT 0;
DECLARE exceptionHash VARCHAR(126);
DECLARE countData INT DEFAULT 0;
DECLARE idx INT DEFAULT 0;
DECLARE dataid INT DEFAULT 0;
DECLARE tempSql1 VARCHAR(126);
DECLARE tempSql2 VARCHAR(126);
DECLARE buildId VARCHAR(126);
set @buildId = buildRecordId;
select @countData:=count(1) from (select count(1) from mytable where time_field = '1970-01-01 08:00:00' and record_id_field = @buildId GROUP BY exception_hash) t;
while idx < @countData DO
	select @exceptionHash:=exception_hash,@dataid:=id from mytable where time_field = '1970-01-01 08:00:00' and record_id_field = buildRecordId GROUP BY exception_hash ORDER BY exception_hash asc limit idx,1;
	
	IF (@exceptionHash=NULL AND @dataid=NULL) THEN 
		set @tempSql1 = concat('select @v1:=sum(exception_count),@v2:=sum(illegal_count),@v3:=sum(force_ground_count),@v4:=sum(back_ground_count),@v5:=sum(so_hotfix_count) from mytable where time_field > "1970-01-01 08:00:00" and time_field < "2019-10-24 00:00:00" and record_id_field = "',@buildId,'" and exception_hash = "',@exceptionHash,'"');
		prepare stmt1 from @tempSql1;
		execute stmt1;
		deallocate prepare stmt1;	

		set @tempSql2 = concat('update mytable set exception_count=',@v1,',illegal_count=',@v2,',force_ground_count=',@v3,',back_ground_count=',@v4,',so_hotfix_count=',@v5,' where id = ',@dataid);
		prepare stmt2 from @tempSql2;
		execute stmt2;
		deallocate prepare stmt2;
		commit;	
	END IF;

	SET idx = idx + 1;
END WHILE;

set rsql = 1;

END