注册
登录
| 关键词： nbsp connectcount hour2 ELSE THEN CASE WHEN END MAX realname |
表结构: 结果: 最近在项目中的一个列转行问题,在这里记录下来!遇到这个问题的园友修改修改可以直接拿走!! create table #tempzjl (realname varchar(50),hour2 INT default(0),connectcount INT default(0)) www.2cto.com insert into #tempzjl select [RealName] as [RealName],datepart(hh,ConnectTime) as hour2,COUNT(isnull(ConnectTime,0)) as ConnectCount from GetSupportorView group by datepart(hh,ConnectTime),[RealName] www.2cto.com insert into #tempzjl (realname,hour2,connectcount) select #tempzjl.realname,-1,sum(connectcount) from #tempzjl group by #tempzjl.realname SELECT realname, MAX(CASE hour2 WHEN 1 THEN connectcount ELSE 0 END) AS '1', MAX(CASE hour2 WHEN 2 THEN connectcount ELSE 0 END) AS '2', MAX(CASE hour2 WHEN 3 THEN connectcount ELSE 0 END) AS '3', MAX(CASE hour2 WHEN 4 THEN connectcount ELSE 0 END) AS '4', MAX(CASE hour2 WHEN 5 THEN connectcount ELSE 0 END) AS '5', MAX(CASE hour2 WHEN 6 THEN connectcount ELSE 0 END) AS '6', MAX(CASE hour2 WHEN 7 THEN connectcount ELSE 0 END) AS '7', MAX(CASE hour2 WHEN 8 THEN connectcount ELSE 0 END) AS '8', MAX(CASE hour2 WHEN 9 THEN connectcount ELSE 0 END) AS '9', MAX(CASE hour2 WHEN 10 THEN connectcount ELSE 0 END) AS '10', MAX(CASE hour2 WHEN 11 THEN connectcount ELSE 0 END) AS '11', MAX(CASE hour2 WHEN 12 THEN connectcount ELSE 0 END) AS '12', MAX(CASE hour2 WHEN 13 THEN connectcount ELSE 0 END) AS '13', MAX(CASE hour2 WHEN 14 THEN connectcount ELSE 0 END) AS '14', MAX(CASE hour2 WHEN 15 THEN connectcount ELSE 0 END) AS '15', MAX(CASE hour2 WHEN 16 THEN connectcount ELSE 0 END) AS '16', MAX(CASE hour2 WHEN 17 THEN connectcount ELSE 0 END) AS '17', MAX(CASE hour2 WHEN 18 THEN connectcount ELSE 0 END) AS '18', MAX(CASE hour2 WHEN 19 THEN connectcount ELSE 0 END) AS '19', MAX(CASE hour2 WHEN 20 THEN connectcount ELSE 0 END) AS '20', MAX(CASE hour2 WHEN 21 THEN connectcount ELSE 0 END) AS '21', MAX(CASE hour2 WHEN 22 THEN connectcount ELSE 0 END) AS '22', MAX(CASE hour2 WHEN 23 THEN connectcount ELSE 0 END) AS '23', MAX(CASE hour2 WHEN 24 THEN connectcount ELSE 0 END) AS '24', MAX(CASE hour2 WHEN -1 THEN connectcount ELSE 0 END) AS '总计' FROM #tempzjl www.2cto.com GROUP BY realname drop table #tempzjl 作者 早念 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
