Transforming multi column table to different structure table

Question

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.

Answer 1

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.