Splitting one row into more rows -- table transformation

Question

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

More background information to explain. I have already successfully tried an insert trigger that does that for a single line:

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

Now, I would like to initialize the destination table from the old table once. After that, the trigger will continue to fill the values.

I am not good in SQL. I tried:

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

... but it fails with

Cannot insert the value NULL into column 'UTC', table '1000574.dbo.line_sensor_values'; column does not allow nulls. INSERT fails.

It is apparent that the SELECT should be used differently to feed the INSERT. Or do I have to use the loop? (Cursor created and FETCH NEXT... and WHILE...)

UPDATED

The source table can be created this way (simplified):

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

The destination table was created this way:

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

Thanks for your help, Petr

Your question is to create a table with four rows, from one with four columns. Why not just insert the values into the table, one-time? The amount of code written indicates a large amount of effort for a small problem. — Gordon Linoff, Aug 09 '12 at 21:36
Yes, this is normalization, yes this is a good thing. What's up with how you're getting your final timestamp, was the original not good enough? And I agree with @Gordon - just use the normalized table (barring _huge_ analysis needs). And you don't need a cursor, or the `VALUES` clause - you can `INSERT` straight from a `SELECT` - although you'll need to use four different ones to separate the data. — Clockwork-Muse, Aug 09 '12 at 22:03

Void Ray · Answer 1 · 2012-08-09T23:20:57.840

If all you need to do is to convert a table with four columns into a single table where each row represents a row number from a source table and a column from a source table, then here is an example:

Here is SQLFiddle

create table fourColumns
(
    column1 varchar(50),
    column2 varchar(50),
    column3 varchar(50),
    column4 varchar(50)
)

insert into fourColumns select 'A','B','C','D'
insert into fourColumns select 'E','F','G','H'

    ;with MyCTE (lineNumber, columnNumber, Result)
as
(
    select ROW_NUMBER() OVER(ORDER BY column1 ASC) AS Row, 1, column1  
    from fourColumns
    union all
    select ROW_NUMBER() OVER(ORDER BY column2 ASC) AS Row, 2, column2  
    from fourColumns
    union all
    select ROW_NUMBER() OVER(ORDER BY column3 ASC) AS Row, 3, column3  
    from fourColumns
    union all
    select ROW_NUMBER() OVER(ORDER BY column4 ASC) AS Row, 4, column4  
    from fourColumns        
)
    -- add insert here
select lineNumber, 
       columnNumber,
       Result
 from MyCTE
 order by lineNumber

score 1 · Accepted Answer · answered Aug 09 '12 at 22:54

INSERT INTO dbo.line_sensor_value
(UTC, line_no, sensor_no, sensor_value)
select UTC, line_no, sensor_no, temperature_1 as sensor_value from dbo.Data
union
select UTC, line_no, sensor_no, temperature_2 as sensor_value from dbo.Data
union
select UTC, line_no, sensor_no, temperature_3 as sensor_value from dbo.Data
union
select UTC, line_no, sensor_no, temperature_4 as sensor_value from dbo.Data

Splitting one row into more rows -- table transformation

2 Answers2

Linked