1.数据如下
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 NUMERIC(12,3)
)
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.更好的解决方案
SELECT
TimePoint,
MAX(CASE WHEN PollutantCode = 'PM2.5' AND StatusName ='大气温度' THEN Value ELSE -99 END) PM2_5大气温度,
MAX(CASE WHEN PollutantCode = 'PM2.5' AND StatusName ='大气压力' THEN Value ELSE -99 END) PM2_5大气温度,
MAX(CASE WHEN PollutantCode = 'PM10' AND StatusName ='大气温度' THEN Value ELSE -99 END) PM10大气温度,
MAX(CASE WHEN PollutantCode = 'PM10' AND StatusName ='大气压力' THEN Value ELSE -99 END) PM10大气压力
FROM #TestTable GROUP BY TimePoint