有一下数据:DataId Data_Number_1 DataType CreateTime
1 13.2 1 2013/1/1
2 14 2 2013/1/1
3 15 3 2013/1/1
把以上三条记录转化成一下一条:
CreateTime DataId1 DataId2 DataId3 Data_Number_1 Data_Number_2 Data_Number_3 DataType1 DataType2 DataType3
2013/1/1 1 2 3 13.2 14 15 1 2 3
请问sql语句怎么写?谢谢。
1 13.2 1 2013/1/1
2 14 2 2013/1/1
3 15 3 2013/1/1
把以上三条记录转化成一下一条:
CreateTime DataId1 DataId2 DataId3 Data_Number_1 Data_Number_2 Data_Number_3 DataType1 DataType2 DataType3
2013/1/1 1 2 3 13.2 14 15 1 2 3
请问sql语句怎么写?谢谢。
http://bbs.csdn.net/topics/330039676
直接调用这个过程就可
create table TEST
(
DATAID VARCHAR2(8) not null,
DATA_NUMBER VARCHAR2(8),
DATATYPE VARCHAR2(8),
CREATETIME DATE
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table TEST
is '行列互转测试表';
-- Create/Recreate primary, unique and foreign key constraints
alter table TEST
add constraint PK_DATAID primary key (DATAID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
SELECT CREATETIME, WMSYS.WM_CONCAT(RESULT) FROM (
SELECT T.CREATETIME, WMSYS.WM_CONCAT (T.DATAID) AS RESULT
FROM TEST T
GROUP BY T.CREATETIME
UNION ALL
SELECT T.CREATETIME, WMSYS.WM_CONCAT (T.DATA_NUMBER) AS RESULT
FROM TEST T
GROUP BY T.CREATETIME
UNION ALL
SELECT T.CREATETIME, WMSYS.WM_CONCAT (T.DATATYPE) AS RESULT
FROM TEST T
GROUP BY T.CREATETIME
)
GROUP BY CREATETIME;