开发者

Select some value knowing column name

I have a table for example with fields(a:int,b:int,c:int,d:int) 开发者_如何学运维I am writing a store procedure. This store procedure will give a string('a' or 'b' or 'c' or 'd') and should give back sum on that column. I am using SQL server 2005.


You can use a CASE expression to choose the appropriate column to summarize

SELECT SUM(
CASE @col -- the param of the stored procedure
WHEN 'a' THEN col_a 
WHEN 'b' THEN col_b 
WHEN 'c' THEN col_c 
ELSE col_d END) AS sum_of_column
FROM my_table

Alternative using dynamic SQL inside the procedure

DECLARE @sql AS VARCHAR(MAX)

SET @sql = 'SELECT SUM(' + @col +') FROM my_table'
EXEC (@sql)


Try to redesign your database so that you're not trying to implement the same operations against multiple columns. Just because SQL tables resemble a spreadsheet, that doesn't mean you should treat it as such.

It sounds like you may have some form of attribute splitting going on - where you have multiple columns representing the same "type" of data, when you should have multiple rows, and an additional column to distinguish these values (e.g. rather than having twelve columns to represent "values" from each month of the year, you should have two columns, storing month and "value").

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜