聯合查詢(姑且稱之爲聯合查詢)的最差解

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.這種不知道算不算行轉列...應該有更好的解決方案...期待有緣人能夠解答...