Tom Cooper
3/28/2007 5:41:00 PM
It is hard to give a complete answer without seeing the entire select
statement and DDL and sample data and the results you want. See
www.aspfaq.com/5006 for instructions on how to do this. That sounds like in
your case that might take some work for you since you are running into that
limit on the number of expressions in the GROUP BY clause, but I think that
is your best hope of getting a complete answer from the group.
If that's not feasible, a way that you could try that often works to solve
this sort of problem is to use a derived table to compute the items so that
you can get rid of the computations in the GROUP BY clause. For example, if
your DDL was
Create Table HOME(ID int, DESCRIPTION varchar(50))
Create Table HOUSE(ID int, DESCRIPTION varchar(50))
Then the following query
SELECT
CASE WHEN (GROUPING(ISNULL(HOME.DESCRIPTION,HOUSE.DESCRIPTION)) = 1)
THEN ' '
ELSE
CONVERT(VARCHAR(100),ISNULL(HOME.DESCRIPTION,HOUSE.DESCRIPTION))
END AS 'Building ',
ISNULL(MAX(HOME.ID),HOUSE.ID) AS 'Building_ID'
FROM HOME
CROSS JOIN HOUSE
GROUP BY
ISNULL(HOME.DESCRIPTION,HOUSE.DESCRIPTION)
,HOUSE.ID
WITH ROLLUP
will get your original error Msg 8120, however you could rewrite it as
SELECT
CASE WHEN (GROUPING(Building)) = 1
THEN ' '
ELSE
Building
END AS 'Building ',
ISNULL(MAX(x.HOMEID),x.HOUSEID) AS 'Building_ID'
FROM (SELECT
CONVERT(VARCHAR(100),ISNULL(HOME.DESCRIPTION,HOUSE.DESCRIPTION))
AS 'Building ',
HOUSE.ID As HOUSEID,
HOME.ID As HOMEID
FROM HOME
CROSS JOIN HOUSE) AS x
GROUP BY
x.Building
,x.HOUSEID
WITH ROLLUP
which will not get the error.
Tom
<raedsawalha@gmail.com> wrote in message
news:1175091126.922000.275410@p15g2000hsd.googlegroups.com...
> The problem i have other group by columns so if i add the missing one
> i will get the following
>
> Server: Msg 1027, Level 15, State 1, Line 279
> Too many expressions are specified in the GROUP BY clause. The maximum
> number is 10 when either CUBE or ROLLUP is specified.
>
>
> So what I can do in this case :)
>