sql - Oracle how to get most common value in a column for multiple columns? -

Suppose i have the following table, how can i group according to id, and get the most common value in each column I can do. The table is large, and I have to do it for many columns

  id call 1 call 2 calls3 .... 1a null 1 ax 1b tap 1i 2 cx 2 cv2a 3 3 bez 3 AZ 3 AZ 3 BX 3 BY  

Expected Result:

  ID Call 1 Call 2 Call 3 .... 1 An Empty 2 CY3 Beas  

using the analytic functions and keeping :

id, select minimum (col1) (dense_rank first order by cnt_col1 desc) as col1_mode, min (col2) (dnt Place the _rank first order cnt_col2 desc as col2_mode, min (col3) as the col3_mode (dense_rank first) as the col3_mode (selection id, number greater than *), (segment by id, col1) as cnt_col1 , Count (*) over (id, partition by col2) cnt_col2, cnt_col3 to t) greater than count (*), (id by id, col3);

The value is often called "mode" in the statistics, and it provides the Oracle one function to calculate. Therefore, an easy way is to select stats_mode () :

  as ID, stats_mode (col1) mode_ col1, as stats_mode (col2) mode_ col2, Stats_mode (col3) IDC by table T group as mode_col3; Edit:  
 As mentioned in a comment,  stats_mode ()  does not believe  NULL  Value. The easiest way to fix this is to find some values ​​that are not in the data and do it: 
 
  select the id, stats_mode (col1, '& lt; null & gt; ')) As mode_col1, stats_mode (col2,' & lt; null) in the form of mode_ col2, stats_mode (col3, '& lt; null & gt;') as a mode_ col3 from table T group to Stats_mode (id) & Gt; '));  
 
 The second way is to return the first approach or something similar: 
 
  Choose ID, (in case when the amount (in case when col1 = mode_col1 then 1 = 0 (in case when the amount (case when col2 = mode_col2 then 1 and 0 end)> = 0 (amount when case 1 is zero then 1 and 0 is the end) then mode_ col1 second tap end) = Sum (case when the call is 2 zero then 1 and 0 end) then mode_ col2 second tap and) as modecall 2, (in case when the amount (when call3 = mode_col13 then 1 and 0 end) & gt; = Sum (in case the call is 3 is zero 1 and 0 mode) as mode_ col3, more than mode_ mode (col2) mode (mode according to mode) as modecode 2 (in ID mode by mode) by ID) Mode_col3) ID by T group;