I am a software developer who got data in the following format:
TABLE [dbo].[SensorValues]( [timestamp] [datetime] NOT NULL, [Sensor1] [float] NULL, [Sensor2] [float] NULL, [Sensor3] [float] NULL )
I would like to transform it to another table, corresponds to the scheme my application expect:
TABLE [dbo].[SensorValuesNormalized]( [Id] [smallint] NOT NULL, [TimeStamp] [datetime2](3) NOT NULL, [Value] [real] NOT NULL )
Id is the number of the column in the original table, the X column will be transform with X as an id.
I can use
Python to go over each line on the source table, and generate rows for Sensor1, Sensor2 and Sensor3.
I wonder if there is an SQL Query I can use to transform data from the first table to the second while retaining the data ?
[timestamp] [Sensor1] [Sensor2] [Sensor3] 2012-10-17 05:47:40.387 | 5.0 | 7.3 | NULL
I would like the output to be:
[Id] [TimeStamp] [Value] 1 2012-10-17 05:47:40.387 5.0 2 2012-10-17 05:47:40.387 7.3
EDIT: I have more than 200 sensors, timestamp is unique in the original table, and not unique in the destination.