列转行

来源：互联网发布：mysql 排序后分组编辑：程序博客网时间：2024/05/02 02:12

问:

表结构如下

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