How to specify GROUB BY in a neat and dynamic way?

1

Hello SQL Team!

I am looking for a neat way to do a GROUP BY so that I do not have to repeat the bunch of fields mentioned in the SELECT. Is it possible?

E.g.

SELECT Col1, Col2, Col3, Col4, count(*) as Count
FROM Table1
GROUP BY 1-4 -- or GROUBY BY 1, 2, 3, 4 or something like that

Is it possible? Because e.g. the Col3 can be a calculated field with several lines of code!
Also, to be able to make the GROUP BY dynamic "e.g. all fields apart from the aggregate", it would be great!

Thanks!

2

The only way would be to write code to dynamically generate the SQL. Obviously that is not what you are looking for - it seems you want a different language altogether.

In general, you write SQL code once (e.g. create a stored procedure, view, function) - and then use that code. It seems you are constantly recreating the same SQL over and over - or even similar code is being rewritten from scratch.

It is quite simple to open a script - make a few modifications to that script - execute it. Takes a few minutes at most. Even with 'complex' calculated columns it is quite easy to structure the code in a way that is easy to copy/paste - or reduce the duplication.

There are CTE's and derived tables, temp tables, table variables and views that can be created and reused. And you can even setup and create templates that prompt for replacement entries, but those take some time and thought to implement.

Examples:

  WITH myCTE
    AS (
SELECT col1
     , col2
     , col3 = <complex calculation>
  FROM schema.table
 WHERE <criteria>
     )
SELECT *
  FROM myCTE
 GROUP BY
       col1
     , col2
     , col3;

In the above - it is very simple to highlight from the beginning of col1 - to the end of col3, copy that selection, paste it in the group by and remove the calculation. Takes just a few seconds...

2 Likes
3

No, there is no way to do that in SQL Server.

1 Like