• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

行转列_sqlserver

sqlserver 搞代码 3年前 (2018-06-16) 67次浏览 已收录 0个评论

问:

表结构如下

time              ne        RegUser  OpenUserCount Sysrate
2006-10-13 15:00WHMSC141.61288254     256915
2006-10-13 15:00WHMSC236.12255287     226457
2006-10-13 15:00WHMSC324.34191782     169963
2006-10-13 15:00WHMSC418.04150165     133202
2006-10-13 15:00WHMSC545.9322620     287878

http://www.gaodaima.com/36094.html行转列_sqlserver

2006-10-13 15:00WHMSC636.5239202     214127
2006-10-13 15:00WHMSC746.81331721     297042
2006-10-13 15:00WHMSC846.38240098     215498
2006-10-13 15:00WHMSC951.58323802     292248
2006-10-13 15:00WHMSCA136.81304640     271097
2006-10-13 15:00WHMSCB36.79316382     278293
2006-10-13 15:00WHMSCC38.69310090     277396
2006-10-13 15:00WHMSCD22.26206749     182311

现在要做成这样
time              ne      字段           值
2006-10-13 15:00WHMSC1  RegUser       41.61
2006-10-13 15:00WHMSC1  OpenUserCount 288254
2006-10-13 15:00  WHMSC1  Sysrate       256915
… 中间省略 …   …     …           …
2006-10-13 15:00WHMSCD  RegUser       22.26
2006-10-13 15:00WHMSCD  OpenUserCount 206749
2006-10-13 15:00  WHMSCD  Sysrate       182311

—-到了下一个小时还是这样,按time,ne分组,如
time              ne      字段           值
2006-10-13 16:00WHMSC1  RegUser       41.61
2006-10-13 16:00WHMSC1  OpenUserCount 288254
2006-10-13 16:00  WHMSC1  Sysrate       256915
… 中间省略 …   …     …           …
2006-10-13 16:00WHMSCD  RegUser       22.26
2006-10-13 16:00WHMSCD  OpenUserCount 206749
2006-10-13 16:00  WHMSCD  Sysrate       182311

———————————————————————-

答:

if object_id(‘pub..tab’) is not null
   drop table tab
go

create table tab
(
time datetime,
ne varchar(10),
ResUser varchar(10),
openusercount varchar(10),
sysrate varchar(10)
)

insert into tab(time,ne,ResUser,openusercount,sysrate) values(‘2006-10-13 15:00′,’WHMSC1′,’41.61′,’288254′,’256915’)
insert into tab(time,ne,ResUser,openusercount,sysrate) values(‘2006-10-13 15:00′,’WHMSC2′,’36.12′,’255287′,’226457’)
insert into tab(time,ne,ResUser,openusercount,sysrate) values(‘2006-10-13 16:00′,’WHMSC1′,’41.62′,’288256′,’256916’)
insert into tab(time,ne,ResUser,openusercount,sysrate) values(‘2006-10-13 16:00′,’WHMSC2′,’36.16′,’255286′,’226456’)

select convert(varchar(16),time,120) as time,ne ,’ResUser’      as 字段,ResUser       as 值 from tab union
select convert(varchar(16),time,120) as time,ne,’openusercount’ as 字段,openusercount as 值 from tab union
select convert(varchar(16),time,120) as time,ne,’sysrate’       as 字段,sysrate       as 值 from tab
order by time,ne

drop table tab

–结果

time             ne         字段            值         
—————- ———- ————- ———-
2006-10-13 15:00 WHMSC1     openusercount 288254
2006-10-13 15:00 WHMSC1     ResUser       41.61
2006-10-13 15:00 WHMSC1     sysrate       256915
2006-10-13 15:00 WHMSC2     openusercount 255287
2006-10-13 15:00 WHMSC2     ResUser       36.12
2006-10-13 15:00 WHMSC2     sysrate       226457
2006-10-13 16:00 WHMSC1     openusercount 288256
2006-10-13 16:00 WHMSC1     ResUser       41.62
2006-10-13 16:00 WHMSC1     sysrate       256916
2006-10-13 16:00 WHMSC2     openusercount 255286
2006-10-13 16:00 WHMSC2     ResUser       36.16
2006-10-13 16:00 WHMSC2     sysrate       226456

(所影响的行数为 12 行)


 

欢迎大家阅读《行转列_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址