使用行转列实现分房间查询

语句如下:

select t2.Mobile,t2.NickName,t1.直播大厅,t1.华尔街,t1.芝加哥南城,t1.芝加哥北城,t1.机器人,t1.尊享大户室
,(t1.直播大厅+t1.华尔街+t1.尊享大户室+t1.机器人+t1.芝加哥北城+t1.芝加哥南城) as 总计
From (

select userid, sum(c.[0]) as '直播大厅',sum(c.[1]) as '华尔街',sum(c.[3]) as '芝加哥北城',sum(c.[4]) as '芝加哥南城',
sum(c.[7]) as '尊享大户室',sum(c.[8]) as '机器人'

from
( SELECT * from ChatMsg PIVOT(count(NickName) FOR LiveRoomId IN ("0","1","3","4","7","8")) a ) as c --where c.CreatedOn>'2016-04-04' and c.CreatedOn<'2016-04-09'
group by c.UserId ) t1
inner join users t2 on t1.UserId=t2.UserId
and t1.UserId=9007

这种方式使用了静态的sql，动态sql暂时还没用到