SQL中列转行的实现

SQL中列转行的实现

表结构:


60_3710_92d9946f502171e.png[设为封面] [删除]        
结果:


60_3710_0af82697b1c54bb.png[设为封面] [删除]  

最近在项目中的一个列转行问题,在这里记录下来!遇到这个问题的园友修改修改可以直接拿走!!

create table  #tempzjl  (realname varchar(50),hour2 INT default(0),connectcount INT default(0))  www.atcpu.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]


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.atcpu.com  
GROUP BY realname

drop table #tempzjl



作者 早念