1

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.

  • 1
    I adapted the title to speak of "insert" instead of "update", since that seems to be what you are looking for. – Erwin Brandstetter Nov 7 '15 at 2:13
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:

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:

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.

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.