title: Splitting one row into more rows -- table transformation
body: <p>I have one source table that should be converted to one destination table. The source table contains four columns with sensor values. The destination table should contain four rows with the single sensor value and with one column for number of the sensor -- for each row from the source table. In other words, the destination table will have four times more rows. (I believe this is called normalization. At least, I think it will be more practical in future when more or less or different sensors are to be used.)</p>
  
  <p>More background information to explain. I have already successfully tried an insert trigger that does that for a single line:</p>
  
  <pre><code>CREATE TRIGGER dbo.temperatures_to_sensors
    ON dbo.Data
    AFTER INSERT
  AS
  BEGIN
    DECLARE @line_no TINYINT;
    SET @line_no = 2;        -- hardwired for the production line
  
    DECLARE @UTC DATETIME;
    DECLARE @value1 FLOAT;
    DECLARE @value2 FLOAT;
    DECLARE @value3 FLOAT;
    DECLARE @value4 FLOAT;
  
    SELECT
        @UTC = CAST((CAST(LEFT(inserted.UTC, 16) AS FLOAT) - 2415020.5) AS DATETIME),
        @value1 = inserted.temperature_1,
        @value2 = inserted.temperature_2,
        @value3 = inserted.temperature_3,
        @value4 = inserted.temperature_4
      FROM inserted;
  
    INSERT INTO dbo.line_sensor_values
           (UTC, line_no, sensor_no, sensor_value)
    VALUES (@UTC, @line_no, 1, @value1),
           (@UTC, @line_no, 2, @value2),
           (@UTC, @line_no, 3, @value3),
           (@UTC, @line_no, 4, @value4);
  END;
  GO
  </code></pre>
  
  <p>Now, I would like to initialize the destination table from the old table once. After that, the trigger will continue to fill the values.</p>
  
  <p>I am not good in SQL. I tried:</p>
  
  <pre><code>CREATE PROCEDURE dbo.init_line_sensor_values
  AS
  BEGIN
    DECLARE @line_no TINYINT;
    SET @line_no = 2;        -- hardwired for the production line
  
    DECLARE @UTC DATETIME;
    DECLARE @value1 FLOAT;
    DECLARE @value2 FLOAT;
    DECLARE @value3 FLOAT;
    DECLARE @value4 FLOAT;
  
    INSERT INTO dbo.line_sensor_values
           (UTC, line_no, sensor_no, sensor_value)
    VALUES (@UTC, @line_no, 1, @value1),
           (@UTC, @line_no, 2, @value2),
           (@UTC, @line_no, 3, @value3),
           (@UTC, @line_no, 4, @value4)
    SELECT
        @UTC = CAST((CAST(LEFT(t.UTC, 16) AS FLOAT) - 2415020.5) AS DATETIME),
        @value1 = t.temperature_1,
        @value2 = t.temperature_2,
        @value3 = t.temperature_3,
        @value4 = t.temperature_4
      FROM dbo.Data AS t;
  
  
  END;
  GO
  
  EXECUTE dbo.init_line_sensor_values
  GO
  </code></pre>
  
  <p>... but it fails with </p>
  
  <blockquote>
    <p>Cannot insert the value NULL into column 'UTC', table '1000574.dbo.line_sensor_values'; column does not allow nulls. INSERT fails.</p>
  </blockquote>
  
  <p>It is apparent that the <code>SELECT</code> should be used differently to feed the <code>INSERT</code>. Or do I have to use the loop? (Cursor created and <code>FETCH NEXT...</code> and <code>WHILE...</code>)</p>
  
  <p><strong>UPDATED</strong></p>
  
  <p>The source table can be created this way (simplified):</p>
  
  <pre><code>CREATE TABLE dbo.Data(
      UTC varchar(32) NOT NULL,
      temperature_1 float NULL,
      temperature_2 float NULL,
      temperature_3 float NULL,
      temperature_4 float NULL
  
  PRIMARY KEY CLUSTERED 
  (
      UTC ASC
  )
  GO
  </code></pre>
  
  <p>The destination table was created this way:</p>
  
  <pre><code>CREATE TABLE dbo.line_sensor_values (
      UTC DATETIME NOT NULL,
      line_no TINYINT NOT NULL,   -- line number: 1, 2, 3, etc.
      sensor_no TINYINT NOT NULL, -- sensor number: 1, 2, 3, etc.
      sensor_value float NULL,    -- the measured value
  
    PRIMARY KEY CLUSTERED (
      UTC ASC,
      line_no ASC,
      sensor_no ASC
    )
  )
  GO
  </code></pre>
  
  <p>Thanks for your help, Petr</p>