i have a table which i want to create a cross table from it. for instance, the table structure is as:

name course score
Jane English 92
Rose Maths 88
Jane Maths 90
Kien Sports 89

and the courses is perhaps uncertain, now i want to reach the result like:

name English maths sports ...
Jane 92 90
Rose 88
Kien 89

if the courses is certain, i can do like this:

create procedure A_TEST_CROSS_TAB
returns
(
name vchar_20,
english vchar_20,
maths vchar_20,
sports vchar_20
)
as
begin
for
select name,
sum(case when course = 'english' then score else null end) as englsih,
sum(case when course = 'maths' then score else null end) as maths,
sum(case when course = 'sports' then score else null end) as sports
from a_test
group by name
into :name, :english, :maths, :sports
do
suspend;

end

----------------------
but now the course is not certain, the store proc returns value cant be set.