There is a table with the name of the fields and the value

How to insert data from this table into the table with the corresponding fields, so that it is like this:

  • Choose one column - jashka
  • I am a newcomer in Oracle, can example show how to do this? - AskarOff Askat

1 answer 1

insert into table1(schet_izv,period,res) select max(decode(param_title,'SCHET_IZV',param_value,NULL)), max(decode(param_title,'PERIOD',param_value,NULL)), max(decode(param_title,'RES',param_value,NULL)) from table2

For Oracle 11g and up:

 insert into table1(schet_izv,period,res) select * from (select param_title, param_value from table2) pivot (max(param_value) for param_title in('SCHET_IZV','PERIOD','RES') )
  • and how can a cycle be done without explicitly specifying the field names? - AskarOff Askat
  • @AskarOffAskat SQL cannot do anything without field names. You first need to create a table with the appropriate fields. names are already needed here. then a cycle - these are 3 references to the same record, and to update by changing its length. At the same time, chained records are formed that ruin all the performance during the subsequent work with such a table. In general, such a perversion can be portrayed by creating queries dynamically, but it is not worth it. If it was needed, it means that the problem is set incorrectly or the task is not for a relational database. - Mike
  • @AskarOffAskat You can try to extract all the fields for pivot like this: select listagg('''' || param_titel || '''' || ' as ' || param_titel, ', ') within group (order by param_titel) pivot_titels from table2 and slip it into IN () dynamically. As Mike noted - is it worth doing? - 0xdb