行转列

问:

表结构如下

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 行）