Início / dba / Perguntas / 120346
Accepted
Franck Dernoncourt
Asked: 2015-11-07 11:37:52 +0800 CST

Selecione a coluna a ser inserida com base no valor da coluna de entrada

  • 772

Suponha as duas tabelas a seguir (e ignore a qualidade do design do banco de dados):

  • A Tabela 1 tem colunas id, sequence_number, value: contém muitas linhas.
  • A Tabela 2 tem as colunas id, seq1, seq2, seq3, seq4, seq5: está vazia.

O objetivo é preencher a Tabela 2 com o valor da Tabela 1, de modo que se na Tabela 1 uma linha for sequence_number = 3; value = 1564.1, então na Tabela 2 deve-se inserir uma linha com seq3 = 1564.1. (as outras colunas além de iddevem estar vazias).

Como fazer isso no SQL? Não quero codificar todas as 5 opções (porque no meu caso de uso real há mais de 50), mas não sei como condicionar o nome da coluna que deve receber o valor com base no valor.

postgresql pivot

2 respostas

  1. Usando instruções CASE..

    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

    Se você considerar esse código rígido, então (até onde eu sei) sua única outra opção seria o SQL dinâmico dentro de um bloco plpgsql.

    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;
    • 2
  2. Best Answer

    Este é basicamente um problema de tabulação cruzada / crosstab / pivô.

    Dadas estas definições de tabela exatas:

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

    Tudo o que você precisa é esta consulta de crosstab básica:

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

    Também a maneira mais rápida que posso pensar.
    Você precisa do módulo adicional tablefunc instalado. Fundamentos:

    Se alguma das suas tabelas tiver mais colunas, seja mais explícito no primeiro parâmetro e/ou adapte para mais colunas:

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

    Mais automação

    Se você precisa fazer isso com frequência, pode automatizar ainda mais:

    Usando o formulário de 2 parâmetros crosstab_n() conforme definido na resposta vinculada , a consulta pode ser tão simples quanto:

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

    Substitua 5 por 50 se você tiver tantas seqNcolunas.

    • 1

relate perguntas