问题如题，
这是我的表结构：
CREATE TABLE BOOK
(
ID NUMBER,
STUNAME VARCHAR2(8),
BOOKNAME VARCHAR(20)
);
SQL 语句如下：
select STUNAME,max(decode(BOOKNAME, 'BOOK1 ',COUNT(BOOKNAME))) as BOOK1
,max(decode(BOOKNAME, 'BOOK2 ',COUNT(BOOKNAME))) as BOOK2
,max(decode(BOOKNAME, 'BOOK3 ',COUNT(BOOKNAME))) as BOOK3
,max(decode(BOOKNAME, 'BOOK4 ',COUNT(BOOKNAME))) as BOOK4
from BOOK
group by STUNAME
union all
select STUNAME,max(decode(BOOKNAME, 'BOOK1 ',COUNT(BOOKNAME)))
,max(decode(BOOKNAME, 'BOOK2 ',COUNT(BOOKNAME)))
,max(decode(BOOKNAME, 'BOOK3 ',COUNT(BOOKNAME)))
,max(decode(BOOKNAME, 'BOOK4 ',COUNT(BOOKNAME)))
from BOOK
group by STUNAME
union all
select STUNAME,max(decode(BOOKNAME, 'BOOK1 ',COUNT(BOOKNAME)))
,max(decode(BOOKNAME, 'BOOK2 ',COUNT(BOOKNAME)))
,max(decode(BOOKNAME, 'BOOK3 ',COUNT(BOOKNAME)))
,max(decode(BOOKNAME, 'BOOK4 ',COUNT(BOOKNAME)))
from BOOK
group by STUNAME;
------解决方案--------------------
--如:
create or replace function test_fun(STUNAMES varchar2) return varchar2 as
v_out varchar2(500):= ' ';
cursor c is select a.BOOKNAME from BOOK a where a.STUNAME=STUNAMES;
begin
for v_temp in c loop
v_out:=v_out|| ' '||v_temp.BOOKNAME;
end loop;
return v_out;
exception
when others then
return 'An error occured ';
end ;
/
select distinct STUNAME,test_fun(STUNAME) BOOK from BOOK