t.apple1,
t.apple2,
t.apple3,
t.apple4,
t.apple5,
t.apple6,
t.apple7,
t.apple8,
t.banana1,
t.banana2,
t.banana3,
t.banana4,
t.banana5,
t.banana6,
t.banana7,
t.banana8
from(
select( select fruitnm frmo TB_CODE c where c.fruitcd=a.fruitcd) as fruitcd,
sum(case when a.fruitkind = 'apple' and a.score='1' then 1 else 0 end) as apple1,
sum(case when a.fruitkind = 'apple' and a.score='2' then 1 else 0 end) as apple2,
sum(case when a.fruitkind = 'apple' and a.score='3' then 1 else 0 end) as apple3,
sum(case when a.fruitkind = 'apple' and a.score='4' then 1 else 0 end) as apple4,
sum(case when a.fruitkind = 'apple' and a.score='4' then 1 else 0 end) as apple5
sum(case when a.fruitkind = 'apple' and a.score='6' then 1 else 0 end) as apple6,
sum(case when a.fruitkind = 'apple' and a.score='7' then 1 else 0 end) as apple7,
sum(case when a.fruitkind = 'apple' and a.score='8' then 1 else 0 end) as apple8,
sum(case when a.fruitkind = 'banana' and a.score='1' then 1 else 0 end) as banana1,
sum(case when a.fruitkind = 'banana' and a.score='2' then 1 else 0 end) as banana2,
sum(case when a.fruitkind = 'banana' and a.score='3' then 1 else 0 end) as banana3,
sum(case when a.fruitkind = 'banana' and a.score='4' then 1 else 0 end) as banana4,
sum(case when a.fruitkind = 'banana' and a.score='4' then 1 else 0 end) as banana5,
sum(case when a.fruitkind = 'banana' and a.score='6' then 1 else 0 end) as banana6,
sum(case when a.fruitkind = 'banana' and a.score='7' then 1 else 0 end) as banana7,
sum(case when a.fruitkind = 'banana' and a.score='8' then 1 else 0 end) as banana8
from TB_FRUIT a
group by a.fruitcd
)t
