SQL: GROUP BY multiple columns with CASE statement

Question

I am new to SQL.

I would like to use GROUP BY with a CASE statement to group the results in a specific way if @myboolean is true.

I have seen many examples of using GROUP BY and CASE BY with one field or how to use GROUP BY with multiple fields without a CASE statement.

I do not know how to combine these two. When I enclose GROUP BY fields in a CASE statement, I get a syntax error:

Invalid syntax near ','

So this works:

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

This results in 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 addressed these issues:

• SQL: Group By with Case Statement for multiple fields : it looks like GROUP BY based on CASE , and not on a group of several. In any case, there were no commas.

• Group by multiple columns and case description : maybe I'm fat, but I don't see how this includes the case statement in GROUP BY

• other less relevant

I am using Microsoft SQL Server Management Studio.

Note that I am inheriting a very complex / long SQL statement that I want to avoid too much. I do not want to split the query into two separate SELECT .

Answer 1

You can 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 not 1 , which evaluates to NULL , which does not affect the result.

If in a stored procedure you can also use IF .

Answer 2

You can try as follows:

 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