一般的行列转换都只针对数值型(int或double类型),这种情况下可以利用聚集函数(如SUM)来实现转置,网上也有比较完善的解决方案,可参考[url]http://blog.chinaunix.net/u3/90603/showart_2017912.html[/url]
但是如果要对字符串类型或日期等其他类型进行转置,情况则复杂很多了,这里进行了一些尝试,不需要借助存储过程或函数,也实现了这个转置。
源表:
[table]
|ROW|PROPERTY|VALUE
|1|name|Andy
|1|address|Beijing
|1|remark|test
|2|name|Bill
|2|address|Shanghai
[/table]
目标表:
[table]
|ROW|name|address|remark
|1|Andy|Beijing|test
|2|Bill|Shanghai|NULL
[/table]
这是个简单的例子,下面的情况还要稍微复杂一些:
源表([color=red]table1[/color]):
[table]
|[color=red]row[/color]|[color=red]code[/color]|[color=red]s[/color]|[color=red]i[/color]|[color=red]d[/color]|[color=red]f[/color]
|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
[/table]
目标表:
[table]
|[color=red]row[/color]|[color=red]name[/color]|[color=red]age[/color]|[color=red]idcard[/color]|[color=red]appdate[/color]
|9000714|Andy|11|88888888|2009-01-02 00:00:00
|9000719|Bill|22|66666666|2008-11-22 00:00:00
[/table]
第一步:先得到下面这个初步转置的中间表
[table]
|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
[/table]
第二部:进行分组聚合,可以得到目标表,sql如下:
对于int型的age字段,可以用sum即可,但是采用下面的写法是为了统一转换的过程(所有类型都通过group_concat来处理),即把int型先转换成char型,用group_concat拼接完成后再转换成int型(datetime型也是这样处理的),这个处理只适用于同一个row只有一个age值:
也可以换成下面的sql:
(appdate字段解释类似)
另外,由于SQL Server和Oracle中没有类似于group_concat的函数,实现起来好像要麻烦很多。
附:相关sql
但是如果要对字符串类型或日期等其他类型进行转置,情况则复杂很多了,这里进行了一些尝试,不需要借助存储过程或函数,也实现了这个转置。
源表:
[table]
|ROW|PROPERTY|VALUE
|1|name|Andy
|1|address|Beijing
|1|remark|test
|2|name|Bill
|2|address|Shanghai
[/table]
目标表:
[table]
|ROW|name|address|remark
|1|Andy|Beijing|test
|2|Bill|Shanghai|NULL
[/table]
这是个简单的例子,下面的情况还要稍微复杂一些:
源表([color=red]table1[/color]):
[table]
|[color=red]row[/color]|[color=red]code[/color]|[color=red]s[/color]|[color=red]i[/color]|[color=red]d[/color]|[color=red]f[/color]
|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
[/table]
目标表:
[table]
|[color=red]row[/color]|[color=red]name[/color]|[color=red]age[/color]|[color=red]idcard[/color]|[color=red]appdate[/color]
|9000714|Andy|11|88888888|2009-01-02 00:00:00
|9000719|Bill|22|66666666|2008-11-22 00:00:00
[/table]
第一步:先得到下面这个初步转置的中间表
[table]
|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
[/table]
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 );