SQL分组统计：由一个表的两列作为轴

构建临时表（第一步也是最重要的一步！）PARTITION 函数是关键

select  * into #Table from (select distinct  NeedType,Source,

count(0) over(PARTITION by Source,NeedType)as Sourcecount

 from [table1]   where NeedType in  (18,19,20,21,33,41) )T 

第一步得出的结果是这样的：我们首先想得到以NeedType为轴的表：

于是我们开始第二步：把纵向表按NeedType转换成横向的表：

 select NeedType , case NeedType 
 when 18 then '是啊' when 19 then '对啊'
 when 20 then '嗯啊' when 21 then '嘿啊'
 when 33 then '哒啊' when 41 then 'BINGO!'

end as name, 
 max(case Source when 0 then Sourcecount  else 0 end) as '0',
 max(case Source when 1 then Sourcecount  else 0 end) as '1',
 max(case Source when 2 then Sourcecount  else 0 end) as '2',
 max(case Source when 3 then Sourcecount  else 0 end) as '3',
 max(case Source when 24 then Sourcecount  else 0 end) as '4',
 max(case Source when 26 then Sourcecount  else 0 end) as '5',
 max(case Source when 4 then Sourcecount  else 0 end) as '6',
 max(case Source when 13 then Sourcecount  else 0 end) as '7',
 max(case Source when 14 then Sourcecount  else 0 end) as '8',
 max(case Source when 15 then Sourcecount  else 0 end) as '9',
 max(case Source when 20 then Sourcecount  else 0 end) as '10',
 max(case Source when 18 then Sourcecount  else 0 end) as '11',
 max(case Source when 25 then Sourcecount  else 0 end) as '12',
 max(case Source when 27 then Sourcecount  else 0 end) as '13',
 max(case Source when 22 then Sourcecount  else 0 end) as '14',
 max(case Source when 8 then Sourcecount  else 0 end) as '15'
 from #Table group by NeedType

 为毛要用MAX函数实在不是太懂，反正就是一个劲的case  when啦！

最后得出的结论还是很正确的：

最后还有2个比较恶心的地方，分组求和：

首先按照NeedType求和：这个就比较简单了，直接Group By就可以了。

 select sum(Sourcecount) as acount,NeedType from #Table 
 where Source in(0,1,2,3,24,26,4,13,14,15,20,18,25,27,22,8 )
 group by NeedType 

剩下的按照Source求和：其实很不爽，因为是需要横向在前端页面展示的，最后搞了一段时间，

人过来催了，没办法，在前端用For循环搞定了，心里终究还是不太爽！

select Source,sum(Sourcecount) as acount  from #Table where
   NeedType in  (18,19,20,21,33,41) group by Source