一般的行列转换都只针对数值型(int或double类型),这种情况下可以利用聚集函数(如SUM)来实现转置,网上也有比较完善的解决方案,可参考
http://blog.chinaunix.net/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 );
分享到:
相关推荐
通过c/c++编写对字符串转置 完美运行 过程简洁
字符串转置,字符串的逆置
本书针对程序设计的初学者,以面向对象的程序设计思想为主线,以通俗易懂的方法介绍C++语言,引导读者以最自然的方式,将人类习惯的面向对象的思维方法运用到程序设计中。主要内容包括程序设计基础知识、类与对象的...
两种方案实现SQL SERVER 转置功能1.拼字符串实现转置2.PIVOT
将查询结果进行二维转置后输出
C++矩阵转置代码!
关于数据结构中矩阵的转置的实现 代码详解
本代码实现CUDA中矩阵的加减以及转置的运算 (总共2个CPP 1个H 1个CU 1个CUH文件)
VB 矩阵的转置 VB 矩阵的转置 VB 矩阵的转置
Excel中行列的转置.pdf 学习资料 复习资料 教学资源
里面包含矩阵运算的类,矩阵加,乘,转置,求逆。而且包含可以将字符串分割转化为矩阵的类。而且还包含弧度与度之间的相互转化类。
矩阵的转置与快速转置皆有,遍历输出对比也在其中,有需要的自取,保证真是可用,小白也能入门!
本代码展示了矩阵的转置,通过提示,输入想要转置的矩阵,完成该矩阵的转置
c语言 转置转置 很好的一个代码转置
Labivew 编写的代码,可以实现二维数组转置的功能
java 矩阵转置 java 矩阵转置 java 矩阵转置
这是一个很简单的C语言小源代码,可以实现线代里的矩阵相乘和矩阵转置的功能,仅供新手参考
矩阵转置的java实现,使用java实现矩阵的转置运算
矩阵的转置
编写并测试3*3矩阵转置函数,使用数组保存3*3矩阵,在使用循环语句实现矩阵元素的行列对调,注意在循环语句中究竟需要对哪些元素进行操作。