列转行，考虑效率

勿用下面的查询语句：
select distinct id,
(select value
from a a2
where a2.id = a1.id
and a2.flag = 'a1') value1,
(select value
from a a2
where a2.id = a1.id
and a2.flag = 'a2') value2,
(select value
from a a2
where a2.id = a1.id
and a2.flag = 'a3') value3
from a a1
因为查询量大的时候，执行太慢了，希望写一个效率高的查询，求高人指教
备注：
create table a(
id number,
value varchar2(10),
flag varchar2(5)
)

insert into a (ID, VALUE, FLAG)
values (1, 'a', 'a1');

insert into a (ID, VALUE, FLAG)
values (1, 'b', 'a2');

insert into a (ID, VALUE, FLAG)
values (1, 'c', 'a3');

insert into a (ID, VALUE, FLAG)
values (2, 'd', 'a1');

insert into a (ID, VALUE, FLAG)
values (2, 'e', 'a2');

insert into a (ID, VALUE, FLAG)
values (2, 'f', 'a3');

insert into a (ID, VALUE, FLAG)
values (3, 'g', 'a1');

insert into a (ID, VALUE, FLAG)
values (3, 'h', 'a2');

insert into a (ID, VALUE, FLAG)
values (3, 'j', 'a3');

[解决办法]
你这是电脑桌面的图片 不能上传的 把图片上传到csdn的相册 然后复制地图网址显示到这
------解决方案--------------------

SQL> select id,wmsys.wm_concat(value)
2 from a
3 group by id;

不知道是不是你想要的结果
[解决办法]
scott@TEST> select distinct a1.id,a1.value value1,a2.value value2,a3.value value3 from a a1,a a2,a a3 where a1.id=a2.id and a2.id=a3.id and a1.flag='a1' and a2.flag='a2' and a3.flag='a3';

ID VALUE VALUE VALUE1
---------- ---------- ---------- ----------
3 g h j
1 a b c
2 d e f