把表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 ;
