Mysql中包含日期、字符串类型的转置

yunvn

一般的行列转换都只针对数值型（int或double类型），这种情况下可以利用聚集函数（如SUM）来实现转置，网上也有比较完善的解决方案，可参考http://blog.iyunv.com/u3/90603/showart_2017912.html
但是如果要对字符串类型或日期等其他类型进行转置，情况则复杂很多了，这里进行了一些尝试，不需要借助存储过程或函数，也实现了这个转置。
源表：

ROW	PROPERTY	VALUE
1	name	Andy
1	address	Beijing
1	remark	test
2	name	Bill
2	address	Shanghai

目标表：

ROW	name	address	remark
1	Andy	Beijing	test
2	Bill	Shanghai	NULL

这是个简单的例子，下面的情况还要稍微复杂一些：
源表(table1)：

row	code	s	i	d	f
9000714	idcard	88888888	NULL	NULL	NULL
9000714	appdate	NULL	NULL	2009-01-02 00:00:00	NULL
9000714	age	NULL	11	NULL	NULL
9000714	name	Andy	NULL	NULL	NULL
9000719	name	Bill	NULL	NULL	NULL
9000719	age	NULL	22	NULL	NULL
9000719	idcard	66666666	NULL	NULL	NULL
9000719	appdate	NULL	NULL	2008-11-22 00:00:00	NULL

目标表：

row	name	age	idcard	appdate
9000714	Andy	11	88888888	2009-01-02 00:00:00
9000719	Bill	22	66666666	2008-11-22 00:00:00

第一步：先得到下面这个初步转置的中间表

row	idcard	name	age	appdate
9000714	NULL	NULL	NULL	2009-01-0200:00:00
9000714	NULL	NULL	11	NULL
9000714	88888888	NULL	NULL	NULL
9000714	NULL	Andy	NULL	NULL
9000719	NULL	NULL	NULL	2008-11-2200:00:00
9000719	NULL	NULL	22	NULL
9000719	66666666	NULL	NULL	NULL
9000719	NULL	Bill	NULL	NULL

SELECT row,
CASE WHEN code = 'idcard' THEN s END AS 'idcard',
CASE WHEN code = 'name' THEN s END AS 'name',
CASE WHEN code = 'age' THEN i END AS 'age',
CASE WHEN code = 'appdate' THEN d END AS 'appdate'
FROM table1
GROUP BY row, code, s, d, i, f


第二部：进行分组聚合，可以得到目标表，sql如下：

SELECT row,
cast(group_concat(CASE WHEN cast(age as char) IS NOT NULL THEN age ELSE NULL END ) as signed) AS 'age',
group_concat(CASE WHEN name IS NOT NULL THEN name END ) AS 'name',
group_concat(CASE WHEN idcard IS NOT NULL THEN idcard END ) AS 'idcard',
cast(group_concat(CASE WHEN cast(appdate as char) IS NOT NULL THEN appdate ELSE NULL END ) as datetime) AS 'appdate'
FROM (
SELECT row,
CASE WHEN code = 'idcard' THEN s END AS 'idcard',
CASE WHEN code = 'name' THEN s END AS 'name',
CASE WHEN code = 'age' THEN i END AS 'age',
CASE WHEN code = 'appdate' THEN d END AS 'appdate'
FROM table1
GROUP BY row, code, s, d, i, f
)t
GROUP BY row


对于int型的age字段，可以用sum即可，但是采用下面的写法是为了统一转换的过程（所有类型都通过group_concat来处理），即把int型先转换成char型，用group_concat拼接完成后再转换成int型（datetime型也是这样处理的），这个处理只适用于同一个row只有一个age值：
cast(group_concat(CASE WHEN cast(age as char) IS NOT NULL THEN age ELSE NULL END ) as signed) AS 'age'

也可以换成下面的sql：

SUM(CASE WHEN age IS NOT NULL THEN age ELSE 0 END ) AS 'age'

（appdate字段解释类似）
另外，由于SQL Server和Oracle中没有类似于group_concat的函数，实现起来好像要麻烦很多。
附：相关sql

CREATE TABLE `table1` (
`row` int(20) default NULL,
`code` varchar(255) default NULL,
`s` varchar(8000) default NULL,
`d` datetime default NULL,
`i` int(20) default NULL,
`f` float default NULL
);
INSERT INTO table1 VALUES (9000714, 'idcard', '88888888', NULL, NULL, NULL );
INSERT INTO table1 VALUES (9000714, 'appdate', NULL, '2009-01-02 00:00:00', NULL, NULL );
INSERT INTO table1 VALUES (9000714, 'age', NULL, NULL, 11, NULL);
INSERT INTO table1 VALUES (9000714, 'name',  'Andy', NULL, NULL, NULL );
INSERT INTO table1 VALUES (9000719, 'name', 'Bill', NULL, NULL, NULL );
INSERT INTO table1 VALUES (9000719, 'age', NULL, NULL, 22, NULL );
INSERT INTO table1 VALUES (9000719, 'idcard', '66666666', NULL, NULL, NULL );
INSERT INTO table1 VALUES (9000719, 'appdate', NULL, '2008-11-22 00:00:00', NULL, NULL );