--没测试select time,ne,'reguser' as 字段,(select reguser from 表 a where a.ne=ne) as 值 from 表
union all
select time,ne,'OpenUserCount' as 字段,(select OpenUserCount from 表 a where a.ne=ne) as 值 from 表
union all
select time,ne,'Sysrate' as 字段,(select Sysrate from 表 a where a.ne=ne) as 值 from 表
union all
select time,ne,'OpenUserCount' as 字段,(select OpenUserCount from 表 a where a.ne=ne) as 值 from 表
union all
select time,ne,'Sysrate' as 字段,(select Sysrate from 表 a where a.ne=ne) as 值 from 表
drop table tab
gocreate table tab
(
time datetime,
ne varchar(10),
ResUser decimal(18,2),
openusercount int,
sysrate int
)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 time,ne ,'ResUser' as 字段,ResUser as 值 from tab union
select time,ne,'openusercount' as 字段,openusercount as 值 from tab union
select time,ne,'sysrate' as 字段,sysrate as 值 from tab
order by time,ne drop table tabtime ne 字段 值
------------------------ ---------- ------------- --------------------
2006-10-13 15:00:00.000 WHMSC1 openusercount 288254.00
2006-10-13 15:00:00.000 WHMSC1 ResUser 41.61
2006-10-13 15:00:00.000 WHMSC1 sysrate 256915.00
2006-10-13 15:00:00.000 WHMSC2 openusercount 255287.00
2006-10-13 15:00:00.000 WHMSC2 ResUser 36.12
2006-10-13 15:00:00.000 WHMSC2 sysrate 226457.00
2006-10-13 16:00:00.000 WHMSC1 openusercount 288256.00
2006-10-13 16:00:00.000 WHMSC1 ResUser 41.62
2006-10-13 16:00:00.000 WHMSC1 sysrate 256916.00
2006-10-13 16:00:00.000 WHMSC2 openusercount 255286.00
2006-10-13 16:00:00.000 WHMSC2 ResUser 36.16
2006-10-13 16:00:00.000 WHMSC2 sysrate 226456.00(所影响的行数为 12 行)
select [time],ne,'reguser' as 字段,RegUser from T
union all
select [time],ne,'OpenUserCount',OpenUserCount from T
union all
select [time],ne,'Sysrate',Sysrate from T
union all select '2006-10-13 15:00' ,'WHMSC2', 36.12, 255287 , 226457
union all select '2006-10-13 15:00' ,'WHMSC3', 24.34, 191782 , 169963
union all select '2006-10-13 15:00' ,'WHMSC4', 18.04, 150165 , 133202
union all select '2006-10-13 15:00' ,'WHMSC5', 45.9, 322620 , 287878
union all select '2006-10-13 15:00' ,'WHMSC6', 36.5 ,239202 , 214127
union all select '2006-10-13 15:00' ,'WHMSC7', 46.81, 331721 , 297042
union all select '2006-10-13 15:00' ,'WHMSC8', 46.38, 240098 , 215498
union all select '2006-10-13 15:00' ,'WHMSC9', 51.58, 323802 , 292248
union all select '2006-10-13 15:00' ,'WHMSCA1', 36.81, 304640 , 271097
union all select '2006-10-13 15:00' ,'WHMSCB', 36.79, 316382 , 278293
union all select '2006-10-13 15:00' ,'WHMSCC', 38.69, 310090 , 277396
union all select '2006-10-13 15:00' ,'WHMSCD', 22.26, 206749 , 182311
select time,ne,'reguser' as 字段,(select top 1 reguser from 表 a where a.ne=ne) as 值 from 表
union all
select time,ne,'OpenUserCount' as 字段,(select top 1 OpenUserCount from 表 a where a.ne=ne) as 值 from 表
union all
select time,ne,'Sysrate' as 字段,(select top 1 Sysrate from 表 a where a.ne=ne) as 值 from 表
order by ne
drop table tab
gocreate 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 行)