I've got a question which I cannot seem to solve at this moment. In my setup I've got multiple voltage sensors which are continuously recording the voltage output. Every time a voltage is being measured by a sensor, the name of the sensor is written in a record (+ time and voltage).
So far so good...
Now I want to have a sort of summary with an overview of the number of times a sensor had a specific voltage measurement. Below an example output which I'm looking for.
I've already tried to work with the count function, problem with this is that it is not clear to me how to create new columns containig only the values of e.g. Sensor1
as gbritton said, would be nice if you provided more data. also try to do it in this format
create table #tesla(voltage int, Sensor varchar(50), measuredatetime datetime)
insert into #tesla
select 100, 'V-Sensor1', getdate() union all
select 200, 'V-Sensor2', getdate() union all
select 100, 'V-Sensor1', getdate() union all
select 300, 'V-Sensor3', getdate() union all
select 200, 'V-Sensor2', getdate() union all
select 110, 'V-Sensor1', getdate() union all
select 230, 'V-Sensor3', getdate()
select *
from
(
select voltage, sensor, count(*) as observations
from #tesla
group by voltage, sensor
) src
pivot
(
sum(observations)
for Sensor in ([V-Sensor1], [V-Sensor2], [V-Sensor3])
) piv;
drop table #tesla
otherwise in order to answer your question, we have to do all of this DDL and DML for you.