求一SQL语句，急，在线等候！ORACLE9I！谢谢
现在的表中有数据
ID VALUE1 VALUE2
1 a 0.1
1 b 0.2
1 c 0.3
2 a 0.1
2 c 0.3
现在我想取得的结果是
ID result1 result2
1 a、b、c 0.1、0.2、0.3
2 a、c 0.1、0.3
其中value1和value2的个数是不定的，只能用一个sql语句实现
[解决办法]
CREATE OR REPLACE TYPE T_LINK AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
)
CREATE OR REPLACE TYPE BODY T_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
BEGIN
SCTX := T_LINK(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR || ', '|| VALUE;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := LTRIM(SELF.STR, ', ');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
CREATE OR REPLACE FUNCTION f_StrLink(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING T_LINK;
先写个聚合函数，然后这样就可以了：
select year
,f_strLink(decode(flag, '1 ',qua, ' ')) as qua
,f_strLink(decode(flag, '2 ',qua, ' ')) as amo
from(
select year, '1 ' as flag
,f_strLink(quarter) as qua
--,f_strLink(amount)
from (select * from tbname order by id,result1,result2)
group by year
union all
select year, '2 ' as flag
--,f_strLink(quarter)
,f_strLink(amount) as qua
from (select * from tbname order by id,result1,result2)
group by year
)
group by year
[解决办法]
create table test(id number,value1 varchar2(10),value2 number(10,1))
insert into test values(1, 'a ',0.1);
insert into test values(1, 'b ',0.2);
insert into test values(1, 'c ',0.3);
insert into test values(2, 'a ',0.1);
insert into test values(2, 'c ',0.3);
commit;
select b.id,
(select ltrim(max(sys_connect_by_path(a.value1, '、 ')), '、 ') value1
from (select a.id,
a.value1,
a.n,
lead(a.n) over(partition by a.id order by a.n) n1
from (select a.id,
a.value1,
row_number() over(order by a.id, a.value1 desc) n
from test a) a) a
start with a.id = b.id
and a.n1 is null
connect by n1 = prior a.n) v1,
(select ltrim(max(sys_connect_by_path(a.value2, '、 ')), '、 ') value2
from (select a.id,
a.value2,
a.n,
lead(a.n) over(partition by a.id order by a.n) n1
from (select a.id,
a.value2,
row_number() over(order by a.id, a.value2 desc) n
from test a) a) a
start with a.id = b.id
and a.n1 is null
connect by n1 = prior a.n) v2
from (select distinct a.id from test a) b
