1

I am still working on sql 2005 to sql 2014 server migration.

With the older server, using COMPUTE BY, I was able to create a report that had 57 columns with some of those column being aggregate fields. There was no limit to the amount of columns I can use in one report(one query).

With the new sql 2014, I am using grouping sets to get the same results but I run into a limitation. I cannot put all my columns into one grouping set and then get the subtotals and the grand total:

Too many expressions are specified in the GROUP BY clause. The maximum number is 32 when grouping sets are supplied.

Is there a way I can supply more columns or is there a way around this limitation?

I have also attached the image to better understand what is going on..Query image

Val K
  • 301
  • 3
  • 9
  • 22
  • More than 32 columns is an awful lot of columns for a table with subtotals. Perhaps not all are needed for aggregation. – Gordon Linoff Feb 23 '16 at 03:06
  • 1
    Not all of them are aggregated columns, yet " grouping set" requires to add them all. – Val K Feb 23 '16 at 06:07
  • . . Consider using `MAX(col) as col` instead of `col` in the `GROUP BY` clauses, for columns that do not need to be aggregated. – Gordon Linoff Feb 24 '16 at 02:11
  • Can you kind of explain what you mean? I will add my query to my original post to better understand what I am asking for – Val K Feb 24 '16 at 16:43
  • This screams, SSAS solution with dimensions needed... I think you're trying to do too much with OLTP. Reference: https://technet.microsoft.com/en-us/library/ms190452(v=sql.105).aspx If it were me, i'd suggest breaking it out into multiple queries as a short-term solution and express the need for an OLAP solution if this kind of thing is common where you work. – maplemale Feb 24 '16 at 18:29
  • The thing is that query gets built dynamically on the fly for the reporting web site. That particular report needs all those columns and I need to find some sort of solution to make it still work with new sql 2014 – Val K Feb 25 '16 at 16:49

0 Answers0