SQL: GROUP BY multiple columns with CASE statement

Question

I'm new to SQL.

I'd like to use GROUP BY with a CASE statement, to group results in a particular way if @myboolean is true.

I've seen many examples of how to use GROUP BY and CASE BY with a single field, or how to use GROUP BY with multiple fields without a CASE statement.

I don't know how to combine the two. When I enclose the GROUP BY fields within the CASE statement, I get a syntax error:

Incorrect syntax near ','

So, this works:

GROUP BY 
/* This works with no enclosing CASE statement */
field1,
field2,
field3,
field4

This produces a syntax error:

GROUP BY 
CASE WHEN (@myboolean=1)
  THEN
    field1,   <-- ERROR HERE:  Incorrect syntax near ','
    field2,
    field3,
    field4
  ELSE 
    field1
  END

I have already looked at these questions:

• SQL: Group By with Case Statement for multiple fields: seems to be a GROUP BY either/or based on CASE, rather than group by multiple. No commas involved anyway.

• Group by Multiple columns and case statement: maybe I'm thick, but I can't see how this includes a case statement in the GROUP BY clause

• various others, less relevant

I'm using Microsoft SQL Server Management Studio.

Please note that I'm inheriting a very complex/long SQL statement, which I want to avoid altering too much. I don't want to split the query into two separate SELECT statements.

Answer 1

You could use...

GROUP BY field1,
    CASE WHEN @myboolean=1 THEN field2 END,
    CASE WHEN @myboolean=1 THEN field3 END,
    CASE WHEN @myboolean=1 THEN field4 END

If @myboolean is not 1 that evaluates to NULL which doesn't affect the result.

If it's in a stored-procedure you can also use an IF.

Answer 2

You can try this way:

IF (@myboolean=1)
BEGIN
SELECT
field1,
field2,
field3,
field4
FROM myTable
GROUP BY
field1,
field2,
field3,
field4

END
ELSE
BEGIN
SELECT
field1
FROM myTable
GROUP BY
field1
END