请教高手，oracle中行列转换。

[cooolchen]

数据库中存在着这样的一些数据

ID date city op
0680013620 2006-8-21 南昌市 AAAA
0680013620 2006-8-21 杭州市 BBBB
0680013620 2007-3-1 广州市 CCCC
0680013620 2006-8-21 南昌市 DDDD
0680013620 2006-8-21 南昌市 EEEE
0680013621 2006-8-21 南昌市 AAAA
0680013621 2006-8-21 杭州市 BBBB
0680013622 2007-3-1 广州市 AAAA
0680013622 2006-8-21 南昌市 BBBB
0680013622 2006-8-21 南昌市 CCCC

想得到如下的结果：

ID city AAAA BBBB CCCC DDDD EEEE
0680013620 南昌市 2006-8-21 2006-8-21 2007-3-1 2006-8-21 2006-8-21
0680013621 南昌市 2006-8-21 2006-8-21
0680013622 广州市 2007-3-1 2006-8-21 2006-8-21

请高手帮帮忙，看看。。。憋了半天了。

[welyngj]

select ID ,city
max(decode(op,'AAAA',date)) AAAA,
max(decode(op,'BBBB',date)) BBBB,
max(decode(op,'CCCC',date)) CCCC,
max(decode(op,'DDDD',date)) DDDD,
max(decode(op,'EEEE',date)) EEEE
from mytab
group by ID,city

[cooolchen]

[Quote=引用 1 楼 welyngj 的回复:]
select ID ,city
max(decode(op,'AAAA',date)) AAAA,
max(decode(op,'BBBB',date)) BBBB,
max(decode(op,'CCCC',date)) CCCC,
max(decode(op,'DDDD',date)) DDDD,
max(decode(op,'EEEE',date)) EEEE
from mytab
group by ID,city
[/Quote]

[cooolchen]

[csuxp2008]

[Quote=引用 1 楼 welyngj 的回复:]
select ID ,city
max(decode(op,'AAAA',date)) AAAA,
max(decode(op,'BBBB',date)) BBBB,
max(decode(op,'CCCC',date)) CCCC,
max(decode(op,'DDDD',date)) DDDD,
max(decode(op,'EEEE',date)) EEEE
from mytab
group by ID,city
[/Quote]

顶一下，最近这种行列转换的问题还挺多的

[小灰狼W]

group by ID,city的话得不到楼主要的那种结果
id是分组了，可是city字段的值应该是每个ID的第一个值

[小灰狼W]

我的答案是：

create table test_c("id" varchar2(20),"date" date,city varchar2(20),op varchar2(20)); 
 
 insert into test_c values ('0680013620',date'2006-8-21','南昌市','AAAA');
 insert into test_c values ('0680013620',date'2006-8-21','杭州市','BBBB');
 insert into test_c values ('0680013620',date'2007-3-1','广州市','CCCC');
 insert into test_c values ('0680013620',date'2006-8-21','南昌市','DDDD');
 insert into test_c values ('0680013620',date'2006-8-21','南昌市','EEEE');
 insert into test_c values ('0680013621',date'2006-8-21','南昌市','AAAA'); 
 insert into test_c values ('0680013621',date'2006-8-21','杭州市','BBBB');
 insert into test_c values ('0680013622',date'2007-3-1','广州市','AAAA'); 
 insert into test_c values ('0680013622',date'2006-8-21','南昌市','BBBB'); 
 insert into test_c values ('0680013622',date'2006-8-21','南昌市','CCCC');  
 
 select "id"  ,max(decode(op,'AAAA',city)) city, 
   max(decode(op,'AAAA',"date")) AAAA, 
   max(decode(op,'BBBB',"date")) BBBB, 
   max(decode(op,'CCCC',"date")) CCCC, 
   max(decode(op,'DDDD',"date")) DDDD, 
   max(decode(op,'EEEE',"date")) EEEE 
 from test_c 
 group by "id"            
id	CITY	AAAA	BBBB	CCCC	DDDD	EEEE
0680013620	南昌市	2006-8-21	2006-8-21	2007-3-1	2006-8-21	2006-8-21
0680013621	南昌市	2006-8-21	2006-8-21			
0680013622	广州市	2007-3-1	2006-8-21	2006-8-21		

你可以试一下

[appleqwfyhw]

古老的题目 自己去谷歌一大把

[nothing2014]

相当古老的题了，我刚问了的。。。。。

[ks_reny]

也可以用case when 语句。oralce 支持case when语句的。

[cooolchen]

怎么老是提示论坛在升级呢？害我结不了贴