mysql行转列和列传行

1，行转列，字符串拆分

 -- 1.1 准备
 CREATE TABLE T1(dt DATETIME,dv VARCHAR(100))
INSERT INTO T1 (dt,dv) VALUES('2017-01-01 00:00:00','1,2,3,4,5,6'),('2017-01-01 00:01:00','10,20,30,40,50');

 -- 序列表
 CREATE TABLE sequence (id INT);
 -- 序列表填充存储过程
 DELIMITER $$

CREATE PROCEDURE `P`()
    BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i<60 DO
		INSERT INTO sequence(id) VALUES(i);
		SET i =i+1;
	END WHILE;	
    END$$
DELIMITER ;

 -- 调用存储过程
  CALL P();

 -- 1.2,查询
SELECT
	DATE_ADD(dt,INTERVAL a.id-1 SECOND) dt,
	
		SUBSTRING_INDEX(SUBSTRING_INDEX(dv,',',a.id), ',', -1)	AS dv
FROM
	sequence a
CROSS JOIN(
	SELECT
		dt,
		CONCAT(dv, ',')AS dv,
		LENGTH(dv)- LENGTH(REPLACE(dv, ',', ''))+ 1 AS size
	FROM
		`T1`  
)b ON a.id <= b.size
ORDER BY dt
-- 结果
dt                   dv      
-------------------  --------
2017-01-01 00:00:00  1       
2017-01-01 00:00:01  2       
2017-01-01 00:00:02  3       
2017-01-01 00:00:03  4       
2017-01-01 00:00:04  5       
2017-01-01 00:00:05  6       
2017-01-01 00:01:00  10      
2017-01-01 00:01:01  20      
2017-01-01 00:01:02  30      
2017-01-01 00:01:03  40      
2017-01-01 00:01:04  50  

2，列传行，字符串拼接

CREATE TABLE T2(id INT,NAME VARCHAR(10));

INSERT INTO T2(id,NAME) VALUES(1,'a'),(1,'b'),(2,'aa'),(2,'cc');

SELECT id,GROUP_CONCAT(NAME) r
FROM T2
GROUP BY id;

-- 结果
    id  r       
------  --------
     1  a,b     
     2  aa,cc   
     
 -- 注意区别于
SELECT id,GROUP_CONCAT(NAME,":") r
FROM T2
GROUP BY id;

    id  r        
------  ---------
     1  a:,b:    
     2  aa:,cc: