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?
Another option which does not require dynamic SQL, but only a CROSS APPLY or two
Just for fun, I add Min, Max, and Avg just to illustrate... Also added a PctOfTotal or Common-Size
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
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:
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
and a.name not in ('c'); to give example of how to ignore a columnselect 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.
sum all the values of the returned columns? By the looks of it, name is probably a text column. Sum of what?