|
create table #sbjc
(
区域 varchar(20),
设备状况 varchar(20),
设备ip varchar(50)
)
insert into #sbjc values('外科','良好','192.168.1.1');
insert into #sbjc values('皮肤科','异常','192.168.1.2');
insert into #sbjc values('五官科','良好','192.168.1.3');
insert into #sbjc values('外科','良好','192.168.1.4');
insert into #sbjc values('外科','异常','192.168.1.5');
insert into #sbjc values('皮肤科','异常','192.168.1.6');
insert into #sbjc values('五官科','异常','192.168.1.7');
insert into #sbjc values('外科','良好','192.168.1.8');
insert into #sbjc values('内科','异常','192.168.1.9');
insert into #sbjc values('内科','良好','192.168.1.10');
insert into #sbjc values('口腔科','异常','192.168.1.13');
insert into #sbjc values('口腔科','良好','192.168.1.14');
insert into #sbjc values('内科','良好','192.168.1.70');
insert into #sbjc values('五官科','良好','192.168.1.71');
select ROW_NUMBER() OVER( partition by 区域 order by 设备状况 desc ) as tt, 区域, 设备状况+设备ip as b
into #cc
from #sbjc
select * from #cc
pivot( min(b) for 区域 in (外科,皮肤科,五官科,内科,口腔科))a |
|