SQL Server - statistical Mode of each column

Question

For a given table, I want a SQL query which returns the statistical mode of each column in a single recordset. I see several ways to do this with aggregation, but they're all single column approaches. Can anyone think of a way to do this without taking the union of as many queries as there are columns? There's no mode() aggregate in SQL Server.

If table #x has 3 columns, I want a single row with 3 columns. Here's an example using SQL Server. It's a lot of heavy lifting, and very much tailored to the table definition. I'm looking for a cleaner, more generalized approach. I might want to do this on different tables at different times.

create table #x (name varchar(20), age int, city varchar(20))
insert into #x values ('Bill', 20, 'NYC')
insert into #x values ('Bill', 15, 'NYC')
insert into #x values ('Mary', 29, 'LA')
insert into #x values ('Bill', 30, 'NYC')
insert into #x values ('Bill', 30, 'NYC')
insert into #x values ('Bill', 20, 'LA')
insert into #x values ('Mary', 20, 'NYC')
insert into #x values ('Joe', 12, 'NYC')
insert into #x values ('Fred', 55, 'NYC')
insert into #x values ('Alex', 41, 'NYC')
insert into #x values ('Alex', 30, 'LA')
insert into #x values ('Alex', 10, 'Chicago')
insert into #x values ('Bill', 20, 'NYC')
insert into #x values ('Bill', 10, 'NYC')

create table #modes (_column varchar(20), _count int, _mode varchar(20))
insert into #modes select top 1 'name' _column, count(*) _count, name _mode from #x group by name order by 2 desc
insert into #modes select top 1 'age' _column, count(*) _count, age _mode from #x group by age order by 2 desc
insert into #modes select top 1 'city' _column, count(*) _count, city _mode from #x group by city order by 2 desc

select name, age, city from (select _mode, _column from #modes) m
pivot (max(_mode) for _column in (name, age, city)) p

Answer 1

This will dynamically generate Item, Value and Hits. You can pivot as you see fit.

Declare @YourTable table (name varchar(20), age int, city varchar(20))
Insert Into @YourTable values
('Bill', 20, 'NYC'),
('Bill', 15, 'NYC'),
('Mary', 29, 'LA'),
('Bill', 30, 'NYC'),
('Bill', 30, 'NYC'),
('Bill', 20, 'LA'),
('Mary', 20, 'NYC'),
('Joe', 12, 'NYC'),
('Fred', 55, 'NYC'),
('Alex', 41, 'NYC'),
('Alex', 30, 'LA'),
('Alex', 10, 'Chicago'),
('Bill', 20, 'NYC'),
('Bill', 10, 'NYC')

---

Declare @XML xml
Set @XML = (Select * from @YourTable for XML RAW)

Select Item,Value,Hits 
 From (
        Select Item,Value,Hits=count(*),RowNr = ROW_NUMBER() over (Partition By Item Order By Count(*) Desc)
         From (
                Select ID    = r.value('@id','int')                         -- Usually Reserved
                      ,Item  = Attr.value('local-name(.)','varchar(100)')
                      ,Value = Attr.value('.','varchar(max)') 
                 From  @XML.nodes('/row') as A(r)
                 Cross Apply A.r.nodes('./@*[local-name(.)!="id"]') as B(Attr)
              ) A
         Group By Item,Value
     ) A
Where RowNr=1

Returns

Item    Value   Hits
age     20      4
city    NYC     10
name    Bill    7