联合查询(姑且称之为联合查询)的最差解

1.数据如下

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

2.要求

12条数据可以变成3条数据,并且列变成(timepoint,pm2_5大气温度,pm2_5大气压力，pm10大气温度,pm10大气压力)

3.建表

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'

4.show your the code(最差解)

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

5.这种不知道算不算行转列…应该有更好的解决方案…期待有缘人可以解答…