Get value of SQL column and use it as column name

Question

is it possible to get a value from a column and use it as column name.

CREATE OR REPLACE PROCEDURE INSERT_INTO_MODELLBAU IS 
BEGIN
MERGE INTO modellbau k
USING (
    SELECT jahr, 
    **monat**
    FROM modell) 
m
ON (k.jahr = m.jahr)
WHEN MATCHED THEN
    UPDATE SET k."monat" = m.menge
WHEN NOT MATCHED THEN
    INSERT (jahr, valueOf('monat'))   <---
    VALUES (m.jahr, m.menge);
end;

My goal is to get the value from 'monat' and use it as a columnname in the update statement. The column 'monat' stores the columnnames.

You'd need to use dynamic SQL (which would require a PL/SQL block). If you edit your question to include a reproducible test case (https://stackoverflow.com/help/minimal-reproducible-example), we can probably help you with how to go about that. It's a little hard to guess from your pseudo-SQL exactly what you want to happen. — Justin Cave, Apr 26 '21 at 12:35
Hallo @JustinCave, i edited my answer. I hope u understand my problem now. — Amine Le, Apr 26 '21 at 13:37

score 2 · Accepted Answer · answered Apr 26 '21 at 12:49

Whitelist the possible columns and use a CASE statement:

CREATE OR REPLACE PROCEDURE INSERT_INTO_MODELLBAU IS 
BEGIN
MERGE INTO al_modellbauk k
USING (
  SELECT bereich,
         jahr,
         quelle,
         einheit,
         menge,
         monat
   FROM  al_modellbau
) m
ON (k.jahr = m.jahr AND k.quelle = m.quelle)
WHEN MATCHED THEN
  UPDATE
  SET k.column1 = CASE m.monat WHEN 'COLUMN1' THEN m.menge ELSE k.column1 END,
      k.column2 = CASE m.monat WHEN 'COLUMN2' THEN m.menge ELSE k.column2 END,
      k.column3 = CASE m.monat WHEN 'COLUMN3' THEN m.menge ELSE k.column3 END
WHEN NOT MATCHED THEN
  INSERT (
    bereich,
    jahr,
    quelle,
    einheit,
    column1,
    column2,
    column3
  )
  VALUES (
    m.bereich,
    m.jahr,
    m.quelle,
    m.einheit,
    CASE m.monat WHEN 'COLUMN1' THEN m.menge ELSE NULL END,
    CASE m.monat WHEN 'COLUMN2' THEN m.menge ELSE NULL END,
    CASE m.monat WHEN 'COLUMN3' THEN m.menge ELSE NULL END
  );
end;
/

Also, you (typically) should NOT put COMMIT statements in a procedure and, instead, should COMMIT from the statement that calls the procedure as that allows you to chain multiple procedures together and COMMIT or ROLLBACK as a block rather than individually.

Thank you for your advice. After whitelisting all the columns manually and calling the procedure i got the following error: "unable to get a stable set of rows in the source tables". Do you know how to fix this problem by chance. — Amine Le, Apr 26 '21 at 13:14
@AmineLe, where you get that error?, in your `MERGE` statement? - if so, check the values of the `SELECT` . you have there duplicated values and PLSQL can't decide to which value apply the `UPDATE`. Use http://dbfiddle.uk/ for create a sample. — Marco Aurelio Fernandez Reyes, Apr 26 '21 at 17:51

Get value of SQL column and use it as column name

1 Answers1