SQL query swap column and row-CodePudding

I would like to know how I can swap the row and column data It is original data

I would like to show like this by SQL query

CodePudding user response：

It appears that you are wanting one line per distinct device_id time combination.

You can do this with some clever uses of the CASE command:

select
  device_id,
  SUM(case when measure_name = 'ntu' then measure_value end) as ntu,
  SUM(case when measure_name = 'shutterspeed' then measure_value end) as shutterspeed,
  SUM(case when measure_name = 'intensity' then measure_value end) as intensity,
  time
from table
group by device_id, time