Can we have dynamic group by clause in sql server?

I have one stored procedure...which has one parameter: @subCustName. This stored proc has one select statement with group by custName, subCustName

Ex开发者_如何学Go:

SELECT custName, subCustName,sum(membership)
FROM CUSTOMER_TABLE
GROUP BY
custName, subCustName

If parameter @subCustName is 'ALL', I dont want to group by subCustName.

How do I acheve that ?

Thanks in advance!

Group on a CASE statement?

SELECT
    CASE WHEN @custName = 'all' THEN '' ELSE custName END AS custName,
    subCustName, sum(membership)
FROM  
    CUSTOMER_TABLE
GROUP BY
    CASE WHEN @custName = 'all' THEN '' ELSE custName END, subCustName

Another example in my answer here: SQL Server 2005/2008 Group By statement with parameters without using dynamic SQL?

Something like this

IF (@subCustName = 'ALL')
    SELECT custName, subCustName, 1
    FROM CUSTOMER_TABLE
ELSE
    SELECT custName, subCustName, sum(membership)
    FROM CUSTOMER_TABLE
    GROUP BY custName, subCustName

or you can build query dynamically