如何在SQL Server中求和多个列的值

SELECT 
    name 
FROM 
    sys.all.column 
WHERE object_id = (SELECT object_id 
                   FROM sys.all_objects 
                   WHERE name ='name of my table' and type = 'TT') 
  AND name NOT IN (list of columns that I don't need)

How do I sum all the values of the returned columns from the preceding SQL query?

如何求和前面SQL查询返回的列的所有值?

3 个解决方案

#1

Another option which does not require dynamic SQL, but only a CROSS APPLY or two

另一个不需要动态SQL的选项，只需要一两个交叉应用

Just for fun, I add Min, Max, and Avg just to illustrate... Also added a PctOfTotal or Common-Size

为了好玩，我加上Min, Max和Avg只是为了说明……还增加了PctOfTotal或通用尺寸

Declare @YourTable table (ID int,CustName varchar(50),Sales_Jan int,Sales_Feb int,Sales_Mar int)
Insert into @YourTable values
(1,'John Smith',25,25,50),
(2,'Jane Doe'  ,35,20,null)

Select A.*
      ,C.*
      ,PctOfTotal = Format(C.Total*1.0/Sum(C.Total) over (),'0.00%')
 From  @YourTable A
 Cross Apply (Select XMLData=cast((Select A.* For XML RAW) as xml)) B
 Cross Apply (
                Select Total = Sum(Value)
                      ,Min   = Min(Value)
                      ,Max   = Max(Value)
                      ,Avg   = Avg(Value)
                 From  (
                        Select Value  = attr.value('.','int') 
                         From  B.XMLData.nodes('/row') as A(r)
                         Cross Apply A.r.nodes('./@*') AS B(attr)
                         Where attr.value('local-name(.)','varchar(100)') Like 'Sales_%'
                         --Or you can Exclude Specific Columns
                         --Where attr.value('local-name(.)','varchar(100)') not in ('ID','CustName')
                       ) S
             ) C

Returns

#2

If I understand correctly, you want to find out some columns from meta tables that you want to sum, and then sum those columns on the given table. You can use dynamic SQL to achieve this:

如果我理解正确，您想要从元表中找出一些要求和的列，然后对给定表中的列进行求和。您可以使用动态SQL来实现这一点:

create table t(a integer, b integer, c integer);

insert into t values(1,2,3);

declare @tab varchar(100);
declare @sql varchar(max);
set @sql = '';
set @tab = 't';

select @sql = @sql + '+' + a.name from sys.all_columns a
inner join 
sys.all_objects b
on a.object_id = b.object_id
where b.name = @tab
and a.name not in ('c');

set @sql = 'select ' + stuff(@sql, 1, 1, '') + ' from ' + @tab;

exec(@sql);

Produces:

生产:

#3

select col1,col2,col3,col4,NVL(col1,0)+NVL(col2,0)+NVL(col3,0)+NVL(col4,0)
from
(select *
from sys.all.column
where object_id =(select object_id from sys.all.object where name ='name of my table') 
  and name not in (list of columns that I dont need).)


   A  |  B   |  Total(col1+col2)
------+------+-------
   1  |  2   |   3
---------------------
   1  |      |   1

Whatever columns you get, sum it and put them as seperate column in the result table.

无论你得到什么列，把它求和，并把它们放在结果表中的seperate列中。

#1