行转列_sqlserver

问:

表结构如下

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

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)