把表t_rows中的数据转换为列显示 create table `t_rows` ( `dt_str` varchar(20) not null, `name` varchar(20) not null, `age` int(11) not null ) engine=innodb default charset=utf8; 表t_rows行显示的结果为 mysql select `dt_str`, `name`, `age` from
把表t_rows中的数据转换为列显示
create table `t_rows` (
`dt_str` varchar(20) not null,
`name` varchar(20) not null,
`age` int(11) not null
) engine=innodb default charset=utf8;
表t_rows行显示的结果为
mysql> select `dt_str`, `name`, `age` from `t_rows`;
+----------+-----------+-----+
| dt_str | name | age |
+----------+-----------+-----+
| 20120610 | name_9881 | 81 |
| 20120609 | name_9882 | 82 |
| 20120608 | name_9883 | 83 |
| 20120607 | name_9884 | 84 |
| 20120606 | name_9885 | 85 |
| 20120605 | name_9886 | 86 |
| 20120604 | name_9887 | 87 |
| 20120603 | name_9888 | 88 |
| 20120602 | name_9889 | 89 |
| 20120601 | name_9890 | 90 |
| 20120531 | name_9891 | 91 |
+----------+-----------+-----+
转换为
图片“列显示.jpg”(文字格式有点乱，只好贴图了)的显示方式
dt_str 20120610 20120609 20120608 20120607 20120606 20120605 20120604 20120603 20120602 20120601 20120531
name name_9881 name_9882 name_9883 name_9884 name_9885 name_9886 name_9887 name_9888 name_9889 name_9890 name_9891
age 81 82 83 84 85 86 87 88 89 90 91
存储过程定义：
delimiter $$
drop procedure if exists `pr_row_to_col`$$
create definer=`root`@`%` procedure `pr_row_to_col`()
comment '将表t_row中的3列(`dt_str`, `name`, `age`)数据转换为列显示'
proc_start:begin
declare _end int default 0;
-- 临时表名
declare _temp_tb_name varchar(255) default 't_temp_rows_to_col';
-- 创建存储列数据的表结构sql
declare _sql_create text;
-- 每列数据的拼接的字符串，因为此例只查询3列(`dt_str`, `name`, `age`)数据
-- 假设每列所有行的拼接字符串不超过text，如果超过可以使用longtext等
declare _res_dt,_res_name,_res_age text;
-- 每个数据的长度定义为varchar(255),如果数据最大长度超过255，则改为最大值即可
declare _dt_str,_name,_age varchar(255) default '';
-- 分隔符
declare _spliter char(1) default ',';
-- 查询所有行数据的游标
declare _cur cursor for select `dt_str`, `name`, `age` from t_rows;
declare continue handler for not found set _end=1;
-- 打开游标
open _cur;
-- 初始化
set _res_dt='';
set _res_name='';
set _res_age='';
-- drop临时表
set @exe_str=concat(drop table if exists ,_temp_tb_name);
prepare stmt from @exe_str;
execute stmt;
deallocate prepare stmt;
-- 创建存储列数据的表结构sql
set _sql_create=concat(create table ,_temp_tb_name,();
set _sql_create=concat(_sql_create,col0 varchar(255) not null,);
set @i=1;
rep_start:repeat
fetch _cur into _dt_str, _name, _age;
if _end=1 then
leave rep_start;
end if;
-- 拼接每列数据的字符串
set _res_dt=concat(_res_dt,',_dt_str,',_spliter);
set _res_name=concat(_res_name,',_name,',_spliter);
set _res_age=concat(_res_age,',_age,',_spliter);
-- 拼接创建表结构字符串
set _sql_create=concat(_sql_create,col,@i, varchar(255) not null,);
set @i=@i+1;
until _end=1 end repeat rep_start;
-- 截取每个字符串最后的分隔符
set _res_dt=substring(_res_dt,1,(length(_res_dt)-1));
set _res_name=substring(_res_name,1,(length(_res_name)-1));
set _res_age=substring(_res_age,1,(length(_res_age)-1));
set _sql_create=substring(_sql_create,1,(length(_sql_create)-1));
-- 拼接创建表结构字符串
set _sql_create=concat(_sql_create,)engine=memory default character set utf8);
-- 关闭游标
close _cur;
-- 创建列数据存储使用的临时表
set @exe_str=_sql_create;
prepare stmt from @exe_str;
execute stmt;
deallocate prepare stmt;
-- 插入列数据，在每列数据前插入了列名
set @exe_str=concat(insert into ,_temp_tb_name, values ('dt_str',,_res_dt,),('name',,_res_name,),('age',,_res_age,));
prepare stmt from @exe_str;
execute stmt;
deallocate prepare stmt;
-- 输出行转列后的数据
set @exe_str=concat(select * from ,_temp_tb_name);
prepare stmt from @exe_str;
execute stmt;
deallocate prepare stmt;
end proc_start$$
delimiter ;