mysql和oracle转换_oracle和mysql的行列转换

CREATE TABLE t(

class1 VARCHAR2(2 BYTE),

calldate DATE,

callcount INTEGER

);

INSERT INTO t(class1, calldate, callcount)

VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);

INSERT INTO t(class1, calldate, callcount)

VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);

INSERT INTO t(class1, calldate, callcount)

VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);

INSERT INTO t(class1, calldate, callcount)

VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);

INSERT INTO t(class1, calldate, callcount)

VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);

INSERT INTO t(class1, calldate, callcount)

VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21);

COMMIT ;

oracle：

WMSYS.WM_CONCAT

SQL> select class1,wmsys.wm_concat(callcount) from t group by class1;

CLASS1 WMSYS.WM_CONCAT(CALLCOUNT)

------ --------------------------------------------------------------------------------

1 40,6

2 77

3 33,9,21

mysql :

group_concat

select class1,group_concat(callcount) from t group class1;

注意：group_concat(callcount)可能被截断，因为有个参数规定了这个结果的长度，如果很长，需要修改这个参数！

ref：http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html[@more@]