sql server - SQL function to return "most common value" for multiple columns in a Group By -

i'm looking find simplest way return common value in multiple column results of select statement grouped. finding online points rank on single item in select or dealing each column separately outside of group by.

sample data:

select 100 "auser",  'a' "instance1", 'm' "instance2"  union select 100, 'b', 'm'  union select 100,'c', 'n'  union select 100, 'b', 'o' union select 200,'d', 'p'  union select 200, 'e', 'p'  union select 200,'f', 'p'  union select 200, 'f', 'q' 

sample data result:

auser   instance1   instance2 100               m 100     b           m 100     c           n 100     b           o 200     d           p 200     e           p 200     f           p 200     f           q 

query logic (how see in head):

select auser, most_common(instance1), most_common(instance2) datasample group auser; 

desired result:

100     b           m 200     f           p 

this approach solving problem uses nested window functions. innermost subquery calculates count each column. next subquery ranks these (using row_number() ). outer query uses conditional aggregation results want:

select auser, max(case when seqnum1 = 1 instance1 end),        max(case when seqnum2 = 1 instance2 end) (select t.*,              row_number() on (partition auser order cnt1 desc) seqnum1,              row_number() on (partition auser order cnt2 desc) seqnum2       (select t.*,                    count(*) on (partition auser, instance1) cnt1,                    count(*) on (partition auser, instance2) cnt2             t            ) t      ) t group auser