原始数据:
select t1.CarModelPKID,t2.DefeatReason, count(*) as count from RT_CustAllocResult t left join vm_carinfo t1 on t1.FrameNo = t.FrameNo left join RT_CustFollowUp t2 on t2.RegNo = t.RegNo where t1.CarModelPKID is not null and t2.DefeatReason is not null group by t2.DefeatReason,CarModelPKID
转置后的数据:
select t.CarModelPKID, case DefeatReason when '00' then count end as colReason1, case DefeatReason when '01' then count end as colReason2, case DefeatReason when '02' then count end as colReason3, case DefeatReason when '03' then count end as colReason4, case DefeatReason when '04' then count end as colReason5, case DefeatReason when '05' then count end as colReason6, case DefeatReason when '06' then count end as colReason7, case DefeatReason when '07' then count end as colReason8, case DefeatReason when '08' then count end as colReason9 from(select t1.CarModelPKID,t2.DefeatReason, count(*) as count from RT_CustAllocResult t left join vm_carinfo t1 on t1.FrameNo = t.FrameNo left join RT_CustFollowUp t2 on t2.RegNo = t.RegNo where t1.CarModelPKID is not null and t2.DefeatReason is not null group by t2.DefeatReason,CarModelPKID) t
数据统计:
select t.CustomerLevel as Item, sum(case DefeatReason when '00' then count end) as colReason1, sum(case DefeatReason when '01' then count end) as colReason2, sum(case DefeatReason when '02' then count end) as colReason3, sum(case DefeatReason when '03' then count end) as colReason4, sum(case DefeatReason when '04' then count end) as colReason5, sum(case DefeatReason when '05' then count end) as colReason6, sum(case DefeatReason when '06' then count end) as colReason7, sum(case DefeatReason when '07' then count end) as colReason8, sum(case DefeatReason when '08' then count end) as colReason9 from(select t.CustomerLevel,t2.DefeatReason, count(*) as count from RT_CustAllocResult t left join RT_CustFollowUp t2 on t2.RegNo = t.RegNo where t.CustomerLevel is not null and t2.DefeatReason is not null group by t2.DefeatReason,t.CustomerLevel) t group by t.CustomerLevel