SELECT DISTINCT
c.drugname,
sum(case when v.visitdate between '2010-10-01'and '2011-09-30' then vd.unit ELSE " " END) as '2554',
sum(case when v.visitdate between '2011-10-01'and '2012-09-30' then vd.unit ELSE " " END) as '2555',
sum(case when v.visitdate between '2012-10-01'and '2013-09-30' then vd.unit ELSE " " END) as '2556'
FROM visitdrug vd
INNER JOIN cdrug c ON vd.drugcode = c.drugcode
INNER JOIN visit v on vd.visitno = v.visitno and vd.pcucode = v.pcucode
WHERE v.visitdate BETWEEN '2010-10-01' AND '2013-09-30'
AND c.drugtype in('10','01')
GROUP BY c.drugname;