How to calculate correlations for several fields at once in Oracle?

Question

Oracle

Good afternoon!
There is a table with fields k1,k2....k100 and it is necessary to calculate the correlation (CORR function) for all possible pairs (as a result 100*100/2-100).
How can correlations be calculated for all pairs, given that the number of field values ​​may vary?
Example: there are 1000 rows in the table, but there are 154 gaps in column k1, so all pairs of indicators in which k1 occurs will have a dimension of 9846.
There was an idea to make an intermediate table of the form
name1 | name2| value1 | value2
k1 | k2 | 1 | 2
k1 | k2 | 1 | 5k1
| k2 | 1 | 6
and then everything should work out through group by , but I didn’t find how to make this from the original table.
Update: Found the Link option .
If there are other suggestions it would be good to know.

Answer 1

but I did not find how to make this from the original table.

I would generate dynamic sql queries on insert, looping through possible combinations of fields,
although it is easier to generate a query text with a call to the appropriate function for each possible pair of fields
, in any case, the correct answer is dynamic sql