查询结果字段介绍：c_ex49是业务员id，tmonth是年-月，tamt是对应月的金额，想要最后查询的效果是：把每个业务员的每个月的金额显示成一行，我这里只到了1-10月份，不过显示还是要显示1-12月的，没有的就显示0
上图使用多行转多列，效果为：
下面是具体步骤：
然后，执行上面的逻辑后，就由最初的多行变成了多列
最初的：
select c_ex49,CONVERT(VarChar(7),c_starttime, 120) as tmonth,SUM(CAST(c_ex9 as float)) as tamt from CRM_CONTRACT
where CONVERT(VarChar(7),c_starttime, 120)>=’2019-01′ and CONVERT(VarChar(7),c_starttime, 120)<=’2019-12′ and c_ex49=’scrmsass_yangli’
group by c_ex49,CONVERT(VarChar(7),c_starttime, 120)
执行后的：
select c_ex49,
sum(oneMonth) as oneMonth,sum(twoMonth) as twoMonth,sum(threeMonth) as threeMonth,sum(fourMonth) as fourMonth,sum(fiveMonth) as fiveMonth,sum(sixMonth) as sixMonth,
sum(sevenMonth) as sevenMonth,sum(eightMonth) as eightMonth,sum(nineMonth) as nineMonth,sum(tenMonth) as tenMonth,sum(elevenMonth) as elevenMonth,sum(telvMonth) as telvMonth from (
select c_ex49,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-01′ then c_ex9 else ‘0.0’ end as float)) ‘oneMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-02′ then c_ex9 else ‘0.0’ end as float)) ‘twoMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-03′ then c_ex9 else ‘0.0’ end as float)) ‘threeMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-04′ then c_ex9 else ‘0.0’ end as float)) ‘fourMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-05′ then c_ex9 else ‘0.0’ end as float)) ‘fiveMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-06′ then c_ex9 else ‘0.0’ end as float)) ‘sixMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-07′ then c_ex9 else ‘0.0’ end as float)) ‘sevenMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-08′ then c_ex9 else ‘0.0’ end as float)) ‘eightMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-09′ then c_ex9 else ‘0.0’ end as float)) ‘nineMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-10′ then c_ex9 else ‘0.0’ end as float)) ‘tenMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-11′ then c_ex9 else ‘0.0’ end as float)) ‘elevenMonth’,
sum(CAST(case when CONVERT(VarChar(7),c_starttime, 120)=’2019-12′ then c_ex9 else ‘0.0’ end as float)) ‘telvMonth’
from CRM_CONTRACT where CONVERT(VarChar(7),c_starttime, 120)>=’2019-01′ and CONVERT(VarChar(7),c_starttime, 120)<=’2019-12′ and c_ex49=’scrmsass_yangli’
group by c_ex49,CONVERT(VarChar(7),c_starttime, 120)
) t1 group by c_ex49
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/137540.html
