Select column to be inserted based on input column value

Question

Assume the following two tables (and ignore the quality of the database design):

• Table 1 has columns id, sequence_number, value: it contains many rows.
• Table 2 has columns id, seq1, seq2, seq3, seq4, seq5: it is empty.

The goal is to fill Table 2 with Table 1's value, such that if in Table 1 one row is sequence_number = 3; value = 1564.1, then in Table 2 one should insert a row with seq3 = 1564.1. (the other columns aside from id should be empty).

How to do that in SQL? I don't want to hard code all 5 options (because in my actual use case there are over 50.), but I don't know how to condition the column name that should receive the value based on the value.

Answer 1

This is basically a problem of cross tabulation / crosstab / pivot.

Given these exact table definitions:

CREATE TABLE tbl1 (
  id int
, sequence_number int
, value numeric
);

CREATE TABLE tbl2 (
  id int
, seq1 numeric
, seq2 numeric
, seq3 numeric
, seq4 numeric
, seq5 numeric
);

All you need is this basic crosstab query:

INSERT INTO tbl2
SELECT * FROM  crosstab (
  'TABLE tbl1'
, 'SELECT generate_series(1,5)'
) t(id int, seq1 numeric, seq2 numeric, seq3 numeric, seq4 numeric, seq5 numeric);

Also the fastest way I can think of.
You need the additional module tablefunc installed. Basics:

• PostgreSQL Crosstab Query

If either of your tables has more columns, be more explicit in the first parameter and / or adapt to more columns:

SELECT * FROM  crosstab (
  'SELECT id, sequence_number, value FROM tbl1'  -- optionally ORDER BY
, 'SELECT generate_series(1,7)'
) t(id int, seq1 numeric, seq2 numeric, seq3 numeric, seq4 numeric
          , seq5 numeric, seq6 numeric, seq7 numeric);

More automation

If you have to do this often you can automate further:

• Dynamic alternative to pivot with CASE and GROUP BY

Using the 2-parameter form of crosstab_n() as defined in the linked answer, the query can be as simple as:

SELECT * FROM crosstab_n('TABLE tbl1', 'SELECT generate_series(1,5)', NULL::tbl2);

Replace 5 with 50 if you have that many seqN columns.

Answer 2

By using CASE statements..

INSERT INTO table2 (id, seq1, seq2, seq3, seq4, seq5)
SELECT
  <SOME ID VALUE>,
  CASE WHEN sequence_number = 1 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 2 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 3 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 4 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 5 THEN value ELSE NULL END
FROM table1

If you consider that hard coding, then (as far as I'm aware) your only other option would be dynamic SQL within a plpgsql block.

DO $$
DECLARE
  row table1%ROWTYPE;
  colName VARCHAR(10);
BEGIN
  FOR row in SELECT * FROM TABLE1 LOOP
    colName := 'seq' || row.sequence_number;
    EXECUTE '<SQL HERE THAT USES colName FOR INSERT>';
  END LOOP
END;
$$ language plpgsql;