mysql行转列

mysql 转储存.sql文件
/*
Navicat MySQL Data Transfer

Source Server : localhost_3306
Source Server Version : 80012
Source Host : localhost:3306
Source Database : test

Target Server Type : MYSQL
Target Server Version : 80012
File Encoding : 65001

Date: 2019-01-17 11:04:54
*/

SET FOREIGN_KEY_CHECKS=0;

---

– Table structure for test

---

DROP TABLE IF EXISTS test;
CREATE TABLE test (
aid int(11) NOT NULL,
data varchar(255) DEFAULT NULL,
id int(11) DEFAULT NULL,
sata varchar(255) DEFAULT NULL,
PRIMARY KEY (aid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

– Records of test

---

INSERT INTO test VALUES (‘1’, ‘201801’, ‘10’, ‘1’);
INSERT INTO test VALUES (‘2’, ‘201801’, ‘11’, ‘2’);
INSERT INTO test VALUES (‘3’, ‘201802’, ‘10’, ‘3’);
INSERT INTO test VALUES (‘4’, ‘201802’, ‘11’, ‘3’);
INSERT INTO test VALUES (‘5’, ‘201803’, ‘10’, ‘1’);
INSERT INTO test VALUES (‘6’, ‘201803’, ‘11’, ‘1’);

查询语句

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
‘MAX(IF(data = ‘’’,
data,
‘’’, sata, 0)) AS ‘’’,
data, ‘’’’
)
) INTO @sql
FROM test;

SET @sql = CONCAT(‘Select id, ‘, @sql,’ From test group by id’);
PREPARE a FROM @sql;
EXECUTE a;
DEALLOCATE PREPARE a;