Query help - i don't want to use a cursor!

[Bell1991]

I need to get the sum of multiple columns in a table. The issue is the columns may change over time and i don't want to have to constantly readjust my code...

I have a table called tbl_CenterInfo

I know how to pull all the columns back:
Select [Name]
from sys.columns
where object_ID = 437576597
Then for each column returned, i want to do a sum on that column.

Any ideas how i can do this without using a cursor?

 

[gmmastros]

First, let me caution you about using hard coded Object_ID's. There are plenty of ways an object id can change. For example, if you open the table in SSMS in design mode and add a column or change a column's data type, SQL Server will create a new table with your changes, copy the data in to it, drop the original, and rename the new table. This would cause the table to have a different object id.

So, instead, it's best to calculate it each time you need it by using the object_id function, like this:

Select [Name] 
from sys.columns 
where object_ID = object_id('YourTableNameHere')

In this case, I would suggest dynamic sql to get your results. Normally people dislike dynamic sql because it can allow for SQL Injection. Since this procedure wouldn't take any user input, SQL Injection is not a concern.

Also, I would use the Information_Schema.Columns view for this. It's a little easier to work with, and allows you to filter for number columns because you cannot sum strings, bits or binary data. Since you are concerned about new columns in the table, then you should also consider that there will be a non-summable column added at some point.

Declare @SQL VarChar(8000)

Set @SQL = ''
Select @SQL = @SQL + 'sum([' + Table_Name + '].[' + Column_Name + ']) As [' + Column_Name + '],'
From   Information_Schema.Columns
Where  Table_Name = 'YourTableNameHere'
       And Data_Type in ('int', 'tinyint','bigint','decimal','numeric','money')

Select @SQL = 'Select ' + Left(@SQL, Len(@SQL)-1) + ' From YourTableNameHere'

Exec (@SQL)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bell1991]

Thanks, that is great! Thanks for the tips as well.

Is there a way to modify it so i have the column name and the sum of that column?

For example:
Column1 1020
Column2 5287
Column3 2587
....

I should have specififed this.

 

[markros]

If you want to see the result like this, then you need to UNPIVOT data.

Using the same idea from George you can do

Declare @SQL VarChar(8000)

Select @SQL = coalesce(@SQL + '
UNION ALL SELECT ','') + 
quotename(Column_Name,'''') + ' as ColumnName, 
sum([' + Table_Name + '].[' + Column_Name + ']) As [' + Column_Name + 
'],'From   Information_Schema.Columns
Where  Table_Name = 'YourTableNameHere'
       And Data_Type in ('int', 'tinyint','bigint','decimal','numeric','money')Select @SQL = 'Select ' + Left(@SQL, Len(@SQL)-1) + 
' From YourTableNameHere'
Exec (@SQL)

I haven't tested, but I think you should be able to correct typos if any if you use PRINT @SQL first.

PluralSight Learning Library

 

[markros]

In the above, ' FROM YourTableName' should be part of the @SQL, so the result will look like

select 'Field1' as ColumnName, sum(Field1) as [Field1] from
YourTableName
UNION ALL SELECT
'Field2' as ColumnName, sum(Field2) as [Field2] from

etc.

E.g. adjust the code above this way.

PluralSight Learning Library

 

[Bell1991]

woo hoo, it worked!! Thanks so much!