sqlserver 多行转多列实例

查询结果字段介绍：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