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

1.数据如下

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.这种不知道算不算行转列...应该有更好的解决方案...期待有缘人可以解答...

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

原文：https://www.cnblogs.com/Mysdm/p/11096790.html