--create table
create table test4
(
id varchar2(4),
type number,
num number,
other1 varchar2(20),
other2 varchar2(20)
)
--insert data
declare
begin
for i in 1 .. 100 loop
insert into test4
select '000'||trunc(dbms_random.value(1,9)),trunc(dbms_random.value(1,9)),trunc(dbms_random.value(1,100)),dbms_random.string('m',5),dbms_random.string('x',5) from dual;
end loop;
commit;
end;
--查询是否有重复数据
select * from test4 a
where rowid > (select min(rowid) from test4 b where a.id = b.id and a.type=b.type)
--select
select id,
max(decode(type,1,num,0)) as 类别1,
max(decode(type,2,num,0)) as 类别2,
max(decode(type,3,num,0)) as 类别3,
max(decode(type,4,num,0)) as 类别4,
max(decode(type,5,num,0)) as 类别5,
max(decode(type,6,num,0)) as 类别6,
max(decode(type,7,num,0)) as 类别7,
max(decode(type,7,num,0)) as 类别8,
max(decode(type,9,num,0)) as 类别9,
other1,
other2
from test4
group by id,other1,other2