1

I'm working on some very simple SQL queries, that are twins of the same query, changing a field. Here an example:

select
field1
,sum(field2)
from table
group by field1

And I have to do this for three fields, summing always field2. this means for example:

select
field3
,sum(field2)
from table
group by field3

And so on. I get bored fastly, so I decided to learn something new, applying a variable, and changing the name of the grouping field only once.

   declare @field varchar (10);
-- use field1, field3, field4
   set @field = 'field1';

select
@field
,sum(field2)
from table
group by @field

And the result is this error:

Each GROUP BY expression must include at least one column that is not an external reference (roughly translated by Google Translator)

Well, I decided to face the group by in the end, so I removed it:

   declare @field varchar (10);
-- use field1, field3, field4
   set @field = 'field1';

select
@field
from table

The result is a column that has no name, and on the rows there are many field1 word as the number of the row, all equals constants, something like:

no name
field1
field1
field1
....
field1

instead of

field1
a
b
c
...
z

So the problems are two:

  • how to add a variable as a column name in a query?
  • how to manage it in a group by?

Thank you in advance for your time!

s__
  • 9,270
  • 3
  • 27
  • 45
  • 1
    SQL Server does not support macro-substitution, you would need to use DYNAMIC SQL for this – John Cappelletti Sep 13 '17 at 14:36
  • 1
    Possible duplicate of [declare variable for query string](https://stackoverflow.com/questions/3833352/declare-variable-for-query-string) – Tanner Sep 13 '17 at 14:53

1 Answers1

6

This can be done dynamically:

declare @sql as nvarchar(max)
declare @field1 varchar (10);
declare @field2 varchar (10);
set @field1 = 'field1';
set @field2 = 'field2';

set @sql = 'select [' + @field1 + '], sum([' + @field2 + ']) from table group by [' + @field1 + ']'
exec(@sql)
cloudsafe
  • 2,444
  • 1
  • 8
  • 24