0

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.

Pangamma
  • 731
  • 12
  • 28
  • 1
    any column is groupable, there are some rules about how to group a column, google to check out the list of rules when grouping by a column. – M.Ali Nov 28 '14 at 17:37
  • 1
    @M.Ali that's not completely true. For instance, a column of `TEXT` , `NTEXT`, `IMAGE` datatype isn't groupable – Lamak Nov 28 '14 at 17:39
  • 1
    I am running into the exact issue Lamak has mentioned. Who ever -1'd this... I'd appreciate it if you undo the down-vote. – Pangamma Nov 28 '14 at 17:42
  • Based on your example error, it looks like what you're actually looking for is which column types can use which aggregate functions, correct? – Dave.Gugg Nov 28 '14 at 17:46
  • Correct. Looks like as long as a column is indexable (as seen in Microsoft SQL Server Management Studio) then it will be available for aggregate function usage. So now the trick is figuring out a query that shows which columns are indexable. – Pangamma Nov 28 '14 at 17:54

1 Answers1

0

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')
Nugsson
  • 196
  • 2
  • 12
  • Thanks a million, sir. I'd up vote if I could. In case of future changes to MsSQL, would you mind citing where you got this list of known aggregate-ready types? – Pangamma Nov 28 '14 at 20:50
  • Not a problem. Quite honestly, though, the list was almost entirely out of my head. I had a glance through a list of types when defining a new table to help me out, so it's possible I could have missed something. – Nugsson Nov 28 '14 at 21:11