Select column to be inserted based on input column value 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.
By using CASE statements..

INSERT INTO table2 (id, seq1, seq2, seq3, seq4, seq5)
SELECT
  &lt;SOME ID VALUE&gt;,
  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 '&lt;SQL HERE THAT USES colName FOR INSERT&gt;';
  END LOOP
END;
$$ language plpgsql;