How to see which SQL Columns are groupable

Question

I am writing a log analyzer tool that provides a list of tables to query dynamically. Then, for each table, I want to allow the user to run a select query where they group based on certain selectable columns. The challenge? Figuring out (dynamically) which columns are of a groupable type. Any help would be appreciated. This might be miss-categorized, but SO is one of the most popular coding sites I know of so it would make sense to have this information available for future lookers.

Any help would be greatly appreciated. Thanks.

Question: How do you (dynamically) tell which columns in a MsSQL table are groupable?

Example Error: Operand data type text is invalid for max operator.

Answer 1

I don't think there's an easy way of doing this, e.g. querying the schema info directly for an IsGroupable property. This therefore feels like a mild hack, but here goes:

SELECT 
    * 
FROM    
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    DATA_TYPE IN ('bigint', 'bit', 'char', 'date', 'datetime', 'datetime2'
                , 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'nchar', 'numeric'
                , 'nvarchar', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time'
                , 'timestamp', 'tinyint', 'uniqueidentifier', 'varchar')