Subject | how to create a cross-table in store procedure |
---|---|

Author | ibmcom2011 |

Post date | 2011-04-16T14:29:47Z |

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.

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.