select p.id comperitorId,p.compcorp competitorName, sum(case when c.kindname = 'ATM' then c.num else 0 end) atm, sum(case when c.kindname = 'CRS' then c.num else 0 end) crs, sum(case when c.kindname = 'VTM' then c.num else 0 end) vtm, sum(case when c.kindname = '清分機' then c.num else 0 end) sorter, sum(case when c.kindname = '軟件' then c.num else 0 end) software, sum(case when c.kindname = '其它' then c.num else 0 end) other, m.iscommunication iscommunication from compproduct c join competitor p on c.competitor_id=p.id join (select f.custid,t.companyid,t.iscommunication from technologycompany t join technology_flow f on f.id=t.flowid) m on c.competitor_id=m.companyid and c.cust_id=m.custid where c.cust_id='7649' group by p.id,c.competitor_id,p.compcorp,m.iscommunication