"Group By *" in a SQL Server 2008 query?

I have a large list of columns in a Select.

To group the result I have to get a开发者_StackOverflowll the items in the select clause and put in the Group By clause.

I wonder if there's any easy way to get all the items there for reference in GROUP BY, such as a GROUP BY *

How can this be done?

The "easy" way is to copy/paste the columns from your SELECT into the GROUP BY. There's no syntactic short cut in SQL for this.

If you're grouping by all columns in your SELECT clause, surely all you're achieving is to make the results unique, in which case shouldn't you omit the GROUP BY clause, and just use SELECT DISTINCT ....

Of course, this then raises the question on why you have duplicate results in your result set anyway, but that's a different question.

There aren't really any magic bullets here. You have to list all the columns you are grouping by in the Group By clause. You can't use wildcards.

If you're not performing any aggregation in your SELECT (sum, avg, count, etc.), they you don't need to have every column in your Group By.

Also -- here's one shortcuts I know of

• Use the Query Designer in SSMS. R-click the window pane where the tables go, and select 'add group-by'.

Other sql-editors besides SSMS may also have good/better features for stuff like this -- check out TOAD for SQL, etc.

As @Joe said in a comment, copy-past is about the easiest it gets. Other than that, there is no built-in way that I know of that will auto-group based on what's being selected.

I drag and drop the columns from the object browser or copy and paste from the select statement.