SELECT v.visitdate,v.pid,vd.diagcode,
max(case when vd.dxtype = '01' then vd.diagcode else '' end )as '01',
max(case when vd.dxtype = '02' then vd.diagcode else '' end )as '02',
max(case when vd.dxtype = '04' then vd.diagcode else '' end )as '04',
max(case when vd.dxtype = '05' then vd.diagcode else '' end )as '05'
FROM visit v
inner join visitdiag vd on v.visitno = vd.visitno
where v.visitdate between '2014-10-01' and '2015-09-30'
group by v.visitno
order by v.pid