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
作者 早念
