3

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 C# / 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 ?


e.g.

[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.

2

As you have a large number of sensors, I would probably create a mapping table. Here's a fuller demo using UNPIVOT, eg

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.Sensors') IS NOT NULL DROP TABLE dbo.Sensors
IF OBJECT_ID('dbo.SensorValues') IS NOT NULL DROP TABLE dbo.SensorValues
IF OBJECT_ID('dbo.SensorValuesNormalized') IS NOT NULL DROP TABLE dbo.SensorValuesNormalized
GO

CREATE TABLE dbo.Sensors(
    sensorId    SMALLINT PRIMARY KEY,
    sensorName  VARCHAR(20) UNIQUE NOT NULL,
    dateAdded   DATETIME DEFAULT GETDATE()
)
GO

CREATE TABLE dbo.SensorValues(
    [timestamp] DATETIME PRIMARY KEY,
    Sensor1     FLOAT NULL,
    Sensor2     FLOAT NULL,
    Sensor3     FLOAT NULL
)
GO

CREATE TABLE dbo.SensorValuesNormalized(
    Id          SMALLINT NOT NULL,
    [timestamp] DATETIME2(3) NOT NULL,
    Value       FLOAT NOT NULL,

    PRIMARY KEY ( [timestamp], id )
)
GO

-- Populate sensor mapping table
;WITH cte AS (
SELECT TOP 250 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.sensors ( sensorId, sensorName )
SELECT rn, 'Sensor' + CAST( rn AS VARCHAR(5) )
FROM cte
GO

-- Create some dummy sensor data for testing
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.sensorValues ( [timestamp],  Sensor1, Sensor2, Sensor3 )
SELECT DATEADD( second, rn, '1 Sep 2014' ), RAND() * rn, RAND() * rn * rn, RAND() * rn * rn * rn
FROM cte
GO

-- Remove a few nulls
UPDATE TOP(100) dbo.sensorValues
SET Sensor3 = NULL

UPDATE TOP(10) dbo.sensorValues
SET Sensor2 = NULL

INSERT INTO dbo.SensorValuesNormalized( id, [timestamp], value )
SELECT s.sensorId, x.[timestamp], x.value
FROM
    (
    SELECT
        sourceColumn, [timestamp], value
    FROM SensorValues sv
    UNPIVOT ( value for sourceColumn In ( Sensor1, Sensor2, Sensor3 ) ) upvt
    ) x
    INNER JOIN dbo.Sensors s ON x.sourceColumn = s.sensorName
GO

SELECT *
FROM sensorValues
WHERE [timestamp] = '2014-09-01 00:00:01.000'

SELECT * FROM dbo.SensorValuesNormalized
ORDER BY [timestamp], id

NB, the UNPIVOT will remove NULLs.

  • Thanks, I wasn't clear about the Id, the Id in the destination table, should be corresponding to the original table column header, 1 <=> Sensor1, 2 <=> Sensor2 etc etc... – Ofiris Sep 8 '14 at 11:47
  • timestamps are unique, I have more than 200 sensors. – Ofiris Sep 8 '14 at 11:53
  • There's a couple of issues with datatypes, eg you are converting from datetime to datetime2, float to real. Is that intentional? Ideally they should be the same. – wBob Sep 8 '14 at 12:28
  • This is intentional indeed, the destination table is required to have these data types. – Ofiris Sep 8 '14 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.