SQL列转行

select
sum(case when type='承保' then count end) As 承保,
sum(case when type='未承保' then count end) As 未承保,
sum(case when type='照会' then count end) As 照会,
sum(case when type='未照会' then count end) As 未照会,
sum(case when type='已回执' then count end) As 已回执,
sum(case when type='未回执' then count end) As 未回执,
sum(case when type='退保' then count end) As 退保,
sum(case when type='受理' then count end) As 受理
from
(
select '承保' type,count(*) count from contract t1
where t1.agentname='杨荣' and TO_char(t1.approvedate,'yyyy-MM-dd')<=TO_char(sysdate,'yyyy-MM-dd')
and TO_char(t1.approvedate,'yyyy-MM-dd')>=TO_char(TO_date('2011-9-1','yyyy-MM-dd'),'yyyy-MM-dd')--承保;
union all
select '未承保' type,count(*) count from contract t1
where t1.agentname='杨荣' and t1.approvedate is NULL and TO_char(t1.bizdate,'yyyy-MM-dd')<=TO_char(sysdate,'yyyy-MM-dd')
and TO_char(t1.bizdate,'yyyy-MM-dd')>=TO_char(TO_date('2011-9-1','yyyy-MM-dd'),'yyyy-MM-dd')--未承保;
union all
select '照会' type,count(*) count  from CONTRACTNOTE a,contract a1 where a.contractnoteid=a1.contractid and a.notestatus like '%N3%'
and a1.agentname='王旭芳' and TO_char(a.startdate,'yyyy-MM-dd')<=TO_char(sysdate,'yyyy-MM-dd') and
TO_char(a.startdate,'yyyy-MM-dd')>=TO_char(TO_date('2001-1-9','yyyy-MM-dd'),'yyyy-MM-dd')--照会;
union all
select '未照会' type,count(*) count from CONTRACTNOTE a,contract a1 where a.contractnoteid=a1.contractid and a.notestatus not like '%N3%'
and a1.agentname='王旭芳' and TO_char(a.startdate,'yyyy-MM-dd')<=TO_char(sysdate,'yyyy-MM-dd') and
TO_char(a.startdate,'yyyy-MM-dd')>=TO_char(TO_date('2001-1-9','yyyy-MM-dd'),'yyyy-MM-dd')--未照会;
union all
select '已回执' type,count(*) count from contract a where a.returnreceiptstatus like '%_Y' and a.agentname='杨荣'
and TO_char(a.returnreceiptdate,'yyyy-MM-dd')<=TO_char(sysdate,'yyyy-MM-dd')
and TO_char(a.returnreceiptdate,'yyyy-MM-dd')>=TO_char(TO_date('2011-9-1','yyyy-MM-dd'),'yyyy-MM-dd')--已回执;
union all
select '未回执' type,count(*) count from contract a where a.returnreceiptstatus like '%_N' and a.agentname='杨荣'
and TO_char(a.returnreceiptdate,'yyyy-MM-dd')<=TO_char(sysdate,'yyyy-MM-dd')
and TO_char(a.returnreceiptdate,'yyyy-MM-dd')>=TO_char(TO_date('2011-9-1','yyyy-MM-dd'),'yyyy-MM-dd')--未回执;
union all
select '退保' type,count(*) count from contract
where agentname='杨荣' and TO_char(SURRENDERDATE,'yyyy-MM-dd')<=TO_char(sysdate,'yyyy-MM-dd')
and TO_char(SURRENDERDATE,'yyyy-MM-dd')>=TO_char(TO_date('2011-9-1','yyyy-MM-dd'),'yyyy-MM-dd')--退保时间（SURRENDERDATE）;
union all
select '受理' type,count(*) count from contract
where agentname='杨荣' and TO_char(ACCEPTDATE,'yyyy-MM-dd')<=TO_char(sysdate,'yyyy-MM-dd')
and TO_char(ACCEPTDATE,'yyyy-MM-dd')>=TO_char(TO_date('2011-9-1','yyyy-MM-dd'),'yyyy-MM-dd')--与受理时间（ACCEPTDATE）;
)