There is a table1 with fields call_id, param0, param1, param2, ...param30 . The param fields take values ​​from 1 to 100.

And there is a second table table2 with fields call_id, theme_code which Ineed to fill out from the first table, The complexity of the task is that for one call_id I need to take each of these param as theme_code, And if one of the param is null, then you don't need to create a new record for call_id

Example: table1:

    callid   | par0 | par1 | par2 | par3 | par4 | par5 | par6 | par7 | par8 | par9 | par10 |
-------------------------------------------------------------------------------------------
    
    1234567  | 24   | 2    | null | 91   | 58   | null | 25   | 19   | 77   | 62   | null  |

table2:

callid   |  theme_code  |
------------------------
1234567  |  24          |
------------------------
1234567  |   2          |
------------------------
1234567  |  91          |
------------------------
1234567  |  58          |
------------------------
1234567  |  25          |
------------------------
1234567  |  19          |
------------------------
1234567  |  77          |
------------------------
1234567  |  62          |

CodePudding user response：

You seem to just want to unpivot the columns from table1 into multiple rows:

select callid, theme_code
from table1
unpivot (
  theme_code
  for par in (par0, par1, par2, par3, par4, par5, par6, par7, par8, par9, par10)
)

fiddle

You can read more about pivoting and unpivoting in this article.

Unless table1 is some kind of staging table, copying that data into table2 would go against normalisation principles, and you might be better off just using that query when you need it, or creating a view based on it (which could be a materialised view).

If you really do want to put the unpivoted data into another table then you can use that query as the basis for an insert or merge statement.