0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for serious players and enthusiasts of poker

CREATE table #yourtable
([entity_id] varchar(20), [state] float, [last_changed] datetime)
;
    
INSERT INTO #yourtable
    ([entity_id], [state], [last_changed])
VALUES
('sensor.temperature_1',	29.2,	'20200731 09:49'),
('sensor.temperature_3',	32.8,	'20200731 09:49'),
('sensor.temperature_2',	29.2,	'20200731 09:49'),
('sensor.temperature_4',	33.5,	'20200731 09:49'),
('sensor.temperature_5',	30.2,	'20200731 09:49'),
('sensor.temperature_6',	30.6,	'20200731 09:49'),
('sensor.temperature_5',	30.1,	'20200731 09:49'),
('sensor.temperature_4',	33.6,	'20200731 09:49'),
('sensor.temperature_6',	30.4,	'20200731 09:51'),
('sensor.temperature_5',	30.2,	'20200731 09:52'),
('sensor.temperature_6',	30.6,	'20200731 09:52'),
('sensor.temperature_4',	33.5,	'20200731 09:52'),
('sensor.temperature_5',	30.1,	'20200731 09:53'),
('sensor.temperature_6',	30.5,	'20200731 09:53'),
('sensor.temperature_6',	30.4,	'20200731 09:54'),
('sensor.temperature_4',	33.6,	'20200731 09:54'),
('sensor.temperature_3',	32.9,	'20200731 09:55'),
('sensor.temperature_4',	33.5,	'20200731 09:55'),
('sensor.temperature_3',	32.8,	'20200731 09:56'),
('sensor.temperature_4',	33.6,	'20200731 09:56'),
('sensor.temperature_6',	30.5,	'20200731 09:57'),
('sensor.temperature_4',	33.5,	'20200731 09:57'),
('sensor.temperature_1',	29.1,	'20200731 09:57'),
('sensor.temperature_5',	29.9,	'20200731 09:58'),
('sensor.temperature_6',	30.2,	'20200731 09:58'),
('sensor.temperature_3',	32.9,	'20200731 09:58'),
('sensor.temperature_1',	29.2,	'20200731 09:58'),
('sensor.temperature_5',	29.8,	'20200731 09:59'),
('sensor.temperature_6',	30.1,	'20200731 09:59'),
('sensor.temperature_4',	33.6,	'20200731 09:59'),
('sensor.temperature_3',	32.8,	'20200731 10:00'),
('sensor.temperature_6',	30,	'20200731 10:01'),
('sensor.temperature_6',	30.1,	'20200731 10:02'),
('sensor.temperature_4',	33.5,	'20200731 10:02'),
('sensor.temperature_5',	29.7,	'20200731 10:03'),
('sensor.temperature_6',	29.9,	'20200731 10:03'),
('sensor.temperature_4',	33.6,	'20200731 10:04'),
('sensor.temperature_6',	30,	'20200731 10:05'),
('sensor.temperature_4',	33.5,	'20200731 10:05')
;
select  
  max(case when entity_id = 'sensor.temperature_1' then state end) sensor_temperature_1,
  max(case when entity_id = 'sensor.temperature_1' then state end) sensor_temperature_1,
  max(case when entity_id = 'sensor.temperature_2' then state end) sensor_temperature_2,
  max(case when entity_id = 'sensor.temperature_3' then state end) sensor_temperature_3,
  max(case when entity_id = 'sensor.temperature_4' then state end) sensor_temperature_4,
  max(case when entity_id = 'sensor.temperature_5' then state end) sensor_temperature_5,
  max(case when entity_id = 'sensor.temperature_6' then state end) sensor_temperature_6
from #yourtable
;

fork query

edited aug 7

created aug 7 anonymous