52rrr,澄城县人民政府网,茶叶新闻
timepoint | pollutantcode | statusname | value |
---|---|---|---|
2019-03-16 01:00:00.000 | pm10 | 大气温度 | 11.096 |
2019-03-16 01:00:00.000 | pm10 | 大气压力 | 102.354 |
2019-03-16 01:00:00.000 | pm2.5 | 大气温度 | 14.525 |
2019-03-16 01:00:00.000 | pm2.5 | 大气压力 | 101.358 |
2019-03-16 02:00:00.000 | pm10 | 大气温度 | 10.134 |
2019-03-16 02:00:00.000 | pm10 | 大气压力 | 102.312 |
2019-03-16 02:00:00.000 | pm2.5 | 大气温度 | 13.883 |
2019-03-16 02:00:00.000 | pm2.5 | 大气压力 | 101.3 |
2019-03-16 03:00:00.000 | pm10 | 大气温度 | 10.368 |
2019-03-16 03:00:00.000 | pm10 | 大气压力 | 102.249 |
2019-03-16 03:00:00.000 | pm2.5 | 大气温度 | 14.033 |
2019-03-16 03:00:00.000 | pm2.5 | 大气压力 | 101.258 |
12条数据可以变成3条数据,并且列变成(timepoint,pm2_5大气温度,pm2_5大气压力,pm10大气温度,pm10大气压力)
if object_id('tempdb..#testtable') is not null drop table #testtable; create table #testtable ( id int identity(1,1), timepoint datetime, pollutantcode varchar(10), statusname nvarchar(50), value varchar(50) ) insert into #testtable(timepoint,pollutantcode,statusname,value) select '2019-03-16 01:00:00.000','pm10', '大气温度','11.096' union select '2019-03-16 01:00:00.000' , 'pm10','大气压力','102.354' union select '2019-03-16 01:00:00.000' , 'pm2.5','大气温度','14.525' union select '2019-03-16 01:00:00.000' , 'pm2.5','大气压力','101.358' union select '2019-03-16 02:00:00.000' , 'pm10','大气温度','10.134' union select '2019-03-16 02:00:00.000' , 'pm10','大气压力','102.312' union select '2019-03-16 02:00:00.000' , 'pm2.5','大气温度','13.883' union select '2019-03-16 02:00:00.000' , 'pm2.5','大气压力','101.3' union select '2019-03-16 03:00:00.000' , 'pm10','大气温度','10.368' union select '2019-03-16 03:00:00.000' , 'pm10','大气压力','102.249' union select '2019-03-16 03:00:00.000' , 'pm2.5','大气温度','14.033' union select '2019-03-16 03:00:00.000' , 'pm2.5','大气压力','101.258'
select a.timepoint,a.value pm2_5大气温度,b.value pm2_5大气压力,d.value pm10大气温度,c.value pm10大气压力 from ( select * from #testtable where statusname = '大气温度' and pollutantcode = 'pm2.5' ) a left join ( select * from #testtable where statusname = '大气压力' and pollutantcode = 'pm2.5' ) b on a.timepoint = b.timepoint left join ( select * from #testtable where statusname = '大气压力' and pollutantcode = 'pm10' ) c on a.timepoint = c.timepoint left join ( select * from #testtable where statusname = '大气温度' and pollutantcode = 'pm10' ) d on a.timepoint = d.timepoint
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
SQL Server免费版的安装以及使用SQL Server Management Studio(SSMS)连接数据库的图文方法
SQL Server 2017 Developer的下载、安装、配置及SSMS的下载安装配置(图文教程详解)
网友评论