CODEWITHSUNDEEP
sql-serverdynamictemp-tables
Nicho247
Nicho247

Reputation: 212

How to SUM dynamic field names from temp table

I have table a with bunch of 0's scattered across many columns which are dynamically named dates (e.g. 2017_09_28). I want to hide the rows where the sum of all of these dynamically named columns nets to 0.

Assuming I have to create a select statement...

SELECT * FROM (
Select *, (dynamic column 1 + dynamic column 2 + dynamic column 3) sum
FROM temp table) sbqy WHERE sum != 0

Not sure how to grab all of the dynamic column names! If there is an alternative solution, all ears, but cannot spell out each dynamic column, too many and will change week to week.

Upvotes: 0

Views: 140

Answers (1)

Artashes Khachatryan
Artashes Khachatryan

Reputation: 387

You can use dynamic sql to create a string and then run it with sp_executesql

declare @cmd varchar(4000) = ''

set @cmd = 'select * from temptable where ' + case when someLogicHere then 'dynamicColomn1' end + case when someLogicHere  then '+ dynamicColomn2' end
 + case when someLogicHere  then 'dynamicColomn3' end + ' !=0'

--print(@cmd)
exec sp_executesql @cmd

Upvotes: 1

Related Questions