T-SQL generating a table report with several Boolean columns

Question

I have a table like this,

ID, 
CrateName,
CompanyName,
IncludeMango,
IncludeFruit,
IncludeThis,
IncludeThat,
DateTimeHero

Now I want to get report of how many columns are true for each Boolean datatype column and how many are false, just like a report, without getting the real data in other columns.

Not sure where to start from :/

Answer 1

Well, you can get names of bit columns of your table from system tables something like this:

select C.name 
from sys.columns as C
    inner join sys.tables as T on T.[object_id] = C.[object_id]
    inner join sys.types as TT on TT.system_type_id = C.system_type_id
where 
    T.name = 'Your_Table_Name'
    and TT.name = 'bit'

When you get your columns names - you can build dynamic query and check - how many rows in each column you need has value 1 - and then compare with total rows count.

Briefly something like (but it should be dynamic according to your column names):

select 
    case 
        when sum(cast(Column_Name_X as bigint)) = count(*) 
        then 1 
        else 0 
    end as Column_Name_X 
from Your_Table