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

Here is one way, using analytic functions and keep:

select id,       min(col1) keep(dense_rank first order by cnt_col1 desc) as col1_mode,       min(col2) keep(dense_rank first order by cnt_col2 desc) as col2_mode,       min(col3) keep(dense_rank first order by cnt_col3 desc) as col3_modefrom (select id,             count(*) over (partition by id, col1) as cnt_col1,             count(*) over (partition by id, col2) as cnt_col2,             count(*) over (partition by id, col3) as cnt_col3      from t     ) tgroup by id;

The most frequent value is called the "mode" in statistics, and Oracle offers a function to calculate this. So, an easier way is to use stats_mode():

   select id,         stats_mode(col1) as mode_col1,         stats_mode(col2) as mode_col2,         stats_mode(col3) as mode_col3  from table t  group by id;

EDIT:

As noted in a comment, stats_mode() does not count NULL values. The easiest way to fix this is to find some value that is not in the data and do:

   select id,          stats_mode(coalesce(col1, '<null>')) as mode_col1,          stats_mode(coalesce(col2, '<null>')) as mode_col2,          stats_mode(coalesce(col3, '<null>')) as mode_col3  from table t  group by id;

The other way is to revert to the first approach or something similar:

select id,       (case when sum(case when col1 = mode_col1 then 1 else 0 end) >= sum(case when col1 is null then 1 else 0 end)             then mode_col1             else NULL        end) as mode_col1,       (case when sum(case when col2 = mode_col2 then 1 else 0 end) >= sum(case when col2 is null then 1 else 0 end)             then mode_col2             else NULL        end) as mode_col2,       (case when sum(case when col3 = mode_col13 then 1 else 0 end) >= sum(case when col3 is null then 1 else 0 end)             then mode_col3             else NULL        end) as mode_col3from (select t.*,             stats_mode(col1) over (partition by id) as mode_col1,             stats_mode(col2) over (partition by id) as mode_col2,             stats_mode(col3) over (partition by id) as mode_col3      from table t     ) tgroup by id;