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

子查询执行结果

与直接使用函数相比，代码量多了一点，也算是方便。