现在的表中有数据
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);