列转行，考虑效率
原数据：
<TABLE BORDER="1">
<TR><TH>&nbsp;&nbsp;&nbsp;</TH><TH>ID</TH><TH>VALUE</TH><TH>FLAG</TH></TR>
<TR><TD>1</TD><TD>1</TD><TD>a</TD><TD>a1</TD></TR>
<TR><TD>2</TD><TD>1</TD><TD>b</TD><TD>a2</TD></TR>
<TR><TD>3</TD><TD>1</TD><TD>c</TD><TD>a3</TD></TR>
<TR><TD>4</TD><TD>2</TD><TD>d</TD><TD>a1</TD></TR>
<TR><TD>5</TD><TD>2</TD><TD>e</TD><TD>a2</TD></TR>
<TR><TD>6</TD><TD>2</TD><TD>f</TD><TD>a3</TD></TR>
<TR><TD>7</TD><TD>3</TD><TD>g</TD><TD>a1</TD></TR>
<TR><TD>8</TD><TD>3</TD><TD>h</TD><TD>a2</TD></TR>
<TR><TD>9</TD><TD>3</TD><TD>j</TD><TD>a3</TD></TR>
</TABLE>
要查询出结果如下形式：

勿用下面的查询语句：
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