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 ...
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