SQL中列转行的实现

表结构:

结果:

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

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

作者 早念