sql - How to get the most occurring values in each column of a table -

sql - How to get the most occurring values in each column of a table -

i need help t-sql , new sql scripting.

i have table little on 70 columns , datawarehouse. of values feeds unknown values. ex: if date value unknown, 19000101 , emailkey = -1, while default value might null.

i need write script retrieves next information

column 1 = name of column [this can hard-coded or retrieved temp table]

column 2 = value has highest frequency of occurrence. [it can null, default value or other valid value]. result of next query: select top 1 hotelkey countvalue dbo.factbooking grouping hotelkey order count(*) desc

column 3 = count of occurring value]. result of next query: select top 1 count() countvalue dbo.factbooking grouping hotelkey order count() desc

column 4 = should show percentage of column 3 against total count]. result of next query: (select top 1 count()from dbo.factbooking grouping hotelkey order count() desc)/( select count(*)from dbo.factbooking)

i want query compute above value columns in table. please allow me know if not clear or if need more information.

i used next query generate info , works fine:

use [database name]

drop table #tempcol

drop table #tempbigquery

create table #tempbigquery (sqlquery varchar(max));

select colid,name colname #tempcol syscolumns id=object_id('factflights') order colid asc declare @counter integer = 1 , @colcount integer = (select count() #tempcol),@rowcount decimal(18,2)= (select count() dbo.factflights) declare @colname varchar(100),@sqlquery nvarchar(max) = '' while @counter < @colcount+3 begin

set @colname = (select colname #tempcol colid = @counter) if @colname not null set @sqlquery = isnull('select columnname,columnkey,countvalue,[percent] ( select top 1 ' + char(39) + @colname + char(39) + ' columnname,' + 'cast(' + @colname + ' varchar(max))' + ' columnkey,count(*) countvalue,(count(*) * 100.00) / ' + cast( @rowcount nvarchar(100)) + ' [percent] dbo.factflights grouping ' + @colname + ' order count(*) desc ) ' + @colname + '_info union ' + char(13),'') --print @sqlquery set @counter = @counter + 1 insert #tempbigquery (sqlquery) values (@sqlquery)

end

select * #tempbigquery

sql sql-server sql-server-2008 tsql