Mysql中包含日期、字符串类型的转置
一般的行列转换都只针对数值型（int或double类型），这种情况下可以利用聚集函数（如SUM）来实现转置，网上也有比较完善的解决方案，可参考http://blog.iyunv.com/u3/90603/showart_2017912.html但是如果要对字符串类型或日期等其他类型进行转置，情况则复杂很多了，这里进行了一些尝试，不需要借助存储过程或函数，也实现了这个转置。
源表：
ROWPROPERTYVALUE1nameAndy1addressBeijing1remarktest2nameBill2addressShanghai
目标表：
ROWnameaddressremark1AndyBeijingtest2BillShanghaiNULL
这是个简单的例子，下面的情况还要稍微复杂一些：
源表(table1)：
rowcodesidf9000714idcard88888888NULLNULLNULL9000714appdateNULLNULL2009-01-02 00:00:00NULL9000714ageNULL11NULLNULL9000714nameAndyNULLNULLNULL9000719nameBillNULLNULLNULL9000719ageNULL22NULLNULL9000719idcard66666666NULLNULLNULL9000719appdateNULLNULL2008-11-22 00:00:00NULL
目标表：
rownameageidcardappdate9000714Andy11888888882009-01-02 00:00:009000719Bill22666666662008-11-22 00:00:00
第一步：先得到下面这个初步转置的中间表
rowidcardnameageappdate9000714NULLNULLNULL2009-01-0200:00:009000714NULLNULL11NULL900071488888888NULLNULLNULL9000714NULLAndyNULLNULL9000719NULLNULLNULL2008-11-2200:00:009000719NULLNULL22NULL900071966666666NULLNULLNULL9000719NULLBillNULLNULL
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 );
页:
[1]