how to sum column i in temp table

Question

SELECT @cinema_count = COUNT(c.[key]) FROM cinemas c

SET @count = 0
WHILE @count < @cinema_count
BEGIN
    SET @count = @count+1
    SET @buffer = 'ALTER TABLE #temptable ADD cinema'+LTRIM(RTRIM(CAST(@count AS VARCHAR)))+' MONEY DEFAULT 0 WITH VALUES'
    EXEC(@buffer)
END

this is my code to alter my #temptable, my #temptable now look like this:

date|cinema1|cinema2|cinema3...to cinema10

i want to sum up the values of my column, the problem is i dont know how to select the cinema from my #temptable

here's my code in selecting the sum of cinema

select @sum = sum('cinema' + CAST(@count as varchar)) from #temptable

Operand data type varchar is invalid for sum operator --error.

hellp me pls..thanks

Answer 1

As you can see from the error message, the string 'cinema' is not being considered as a field name, but as a literal string. So you need to use a string variable to concat the variable name with your SUM query. Should be something like this:

DECLARE @SUM VARCHAR(5000)
SET @SUM = 'SELECT SUM(cinema'+CAST(@count AS VARCHAR(50))+') from #temptable'
EXEC (@SUM)

Test dynamic sql by using PRINT in place of EXEC to make sure the resulting query is correct.

Edit: Added missing parenthesis.