sql行转列应用

sql行转列应用

数据表实际存储格式

原始表 为了适应我们某个平台框架的使用（该框架直接配置sql，平台自动实现数据的返回。不写任何后端代码），同时方便前端不做任何处理。就采用sql的方式对元数据进行-行转列运用。 需求：

返回前端的数据结构 需求翻译：同一个因子，对应两个监测值，一个是value,一个是aqi。需要将同一时间，不同的监测因子以及监测因子对应的两个值，转为一行返回给前端。

解决方案

1.利用PIVOT函数（简单方便）

SELECT t1. TIME, t1.no2aqi, t1.so2aqi, t1.coaqi, t2.no2value, t2.so2value, t2.covalue FROM ( SELECT [so2] AS so2aqi, [no2] AS no2aqi, co AS coaqi, TIME FROM ( SELECT t.[name], t.[aqi], t. TIME FROM test_aqi t ) p PIVOT ( MAX ([aqi]) FOR [name] IN ([so2], [no2], co) ) AS pvt ) t1 LEFT JOIN ( SELECT [so2] AS so2value, [no2] AS no2value, co AS covalue, TIME FROM ( SELECT t.[name], t.[value], t. TIME FROM test_aqi t ) p PIVOT ( MAX ([value]) FOR [name] IN ([so2], [no2], co) ) AS pvt ) t2 ON t1.[time] = t2.[time]

PIVOT函数运行实现 简单兼完美的实现

2.利用子查询实现

SELECT t.[time], ( SELECT h.aqi FROM test_aqi h WHERE h. TIME = t.[time] AND h.name = 'no2' ) AS no2aqi, ( SELECT h.aqi FROM test_aqi h WHERE h. TIME = t.[time] AND h.name = 'so2' ) AS so2aqi, ( SELECT h.aqi FROM test_aqi h WHERE h. TIME = t.[time] AND h.name = 'co' ) AS coaqi, ( SELECT h. VALUE FROM test_aqi h WHERE h. TIME = t.[time] AND h.name = 'no2' ) AS no2value, ( SELECT h. VALUE FROM test_aqi h WHERE h. TIME = t.[time] AND h.name = 'so2' ) AS so2value, ( SELECT h. VALUE FROM test_aqi h WHERE h. TIME = t.[time] AND h.name = 'co' ) AS covalue FROM test_aqi t GROUP BY t. TIME

子查询执行结果 与直接使用函数相比，代码量多了一点，也算是方便。