This is My table :
这是我的表:
ID Q1 Q2 Q3 Q4----------------------------------------------------------------20130712 NULL 728.63 NULL NULL20130712 8881.55 9673.68 2629.566 6251.98420130713 1813 1813 84.49 1728.5120130714 3632.65 3632.65 1209.412 2423.23820130714 70.758 2637.43 70.758 020130714 1601.578 3569.73 204.745 1396.83320130714 728.63 728.63 0 728.6320130714 1401.629 2251.39 94.418 1307.21120130715 583.956 5089.19 583.956 020130805 6317.277 8958 2629.566 3687.711I want the output like below.(The columns might change dynamically, needs to calculate Sum by row wise)
我想要输出如下。(列可能会动态更改,需要按行计算Sum)
ID Q1 Q2 Q3 Q4 SUM(Q1:Q4)---------------------------------------------------------------------------20130712 NULL 728.63 NULL NULL 728.63 20130712 8881.55 9673.68 2629.566 6251.984 27436.7820130713 1813 1813 84.49 1728.51 543920130714 3632.65 3632.65 1209.412 2423.238 ...20130714 70.758 2637.43 70.758 020130714 1601.578 3569.73 204.745 1396.83320130714 728.63 728.63 0 728.6320130714 1401.629 2251.39 94.418 1307.21120130715 583.956 5089.19 583.956 020130805 6317.277 8958 2629.566 3687.7113 个解决方案
#1
7
You haven't shown your query attempt, but it's probably something like this:
您没有显示您的查询尝试,但它可能是这样的:
SELECT ID, Q1, Q2, Q3, Q4, Q1 + Q2 + Q3 + Q4 AS "Total"FROM MyTableIf any of the Q1, Q2, Q3, or Q4 values are null, Q1 + Q2 + Q3 + Q4 will be null. To treat the nulls as zero and get a proper sum, do this instead:
如果Q1,Q2,Q3或Q4值中的任何一个为空,则Q1 + Q2 + Q3 + Q4将为空。要将空值视为零并获得正确的总和,请执行以下操作:
SELECT ID, Q1, Q2, Q3, Q4, COALESCE(Q1,0) + COALESCE(Q2,0) + COALESCE(Q3,0) + COALESCE(Q4,0) AS "Total"FROM MyTableThe COALESCE function will return the first non-null value in the list.
COALESCE函数将返回列表中的第一个非空值。
#2
3
Don't know if it there's a shorter way, but the most elegant I can do is:
不知道它是否有更短的路,但我能做的最优雅的是:
select ID, Q1, Q2, Q3, Q4, ( select sum(S.Q) from (values (Q1), (Q2), (Q3), (Q4)) as S(Q) where S.Q is not null ) as [Total]from Table1 as Tsql小提琴演示
If you want dynamic SQL, try something like
如果你想要动态SQL,试试类似的东西
declare @stmt nvarchar(max), @stmt1 nvarchar(max)select @stmt = isnull(@stmt + ', ', '') + name, @stmt1 = isnull(@stmt1 + ', ', '') + '(' + name + ')'from sys.columnswhere object_id = object_id('Table1') and name not in ('ID')select @stmt = 'select ID, ' + @stmt + ', (select sum(S.Q) from (values ' + @stmt1 + ') as S(Q) where S.Q is not null) as [Total] ' + 'from Table1 as T'exec sp_executesql @stmt = @stmtsql小提琴演示
#3
0
Expanding on Roman Pekar, If you're using a temp table and want to do this, you need to use tempdb like this:
扩展到Roman Pekar,如果你正在使用临时表并想要这样做,你需要像这样使用tempdb:
select@stmt = isnull(@stmt + ', ', '') + '[' + name + ']',@stmt1 = isnull(@stmt1 + ', ', '') + '(' + '[' + name + ']'+ ')'from tempdb.sys.columnswhere object_id = object_id('tempdb..##TempTable') and name not in ('ID')--ID would be one of the column names you DONT want to sum.--also notice the double pound sign. you need to declare your temp table with double pounds or it wont work--also notice how I put brackets around name, that's because my columns weren't working because they had slashes in their names.--the rest of the code is the sameselect @stmt = 'select Date_Packed, ' + @stmt + '' + ', (select sum(S.Q) from (values ' + @stmt1 + ') as S(Q) where S.Q is not null) as [Total] ' + 'from tempdb..##TempTableas T' print @stmtexec sp_executesql @stmt = @stmt--don't forget to drop it drop table ##TempTable#1
7
You haven't shown your query attempt, but it's probably something like this:
您没有显示您的查询尝试,但它可能是这样的:
SELECT ID, Q1, Q2, Q3, Q4, Q1 + Q2 + Q3 + Q4 AS "Total"FROM MyTableIf any of the Q1, Q2, Q3, or Q4 values are null, Q1 + Q2 + Q3 + Q4 will be null. To treat the nulls as zero and get a proper sum, do this instead:
如果Q1,Q2,Q3或Q4值中的任何一个为空,则Q1 + Q2 + Q3 + Q4将为空。要将空值视为零并获得正确的总和,请执行以下操作:
SELECT ID, Q1, Q2, Q3, Q4, COALESCE(Q1,0) + COALESCE(Q2,0) + COALESCE(Q3,0) + COALESCE(Q4,0) AS "Total"FROM MyTableThe COALESCE function will return the first non-null value in the list.
COALESCE函数将返回列表中的第一个非空值。
#2
3
Don't know if it there's a shorter way, but the most elegant I can do is:
不知道它是否有更短的路,但我能做的最优雅的是:
select ID, Q1, Q2, Q3, Q4, ( select sum(S.Q) from (values (Q1), (Q2), (Q3), (Q4)) as S(Q) where S.Q is not null ) as [Total]from Table1 as Tsql小提琴演示
If you want dynamic SQL, try something like
如果你想要动态SQL,试试类似的东西
declare @stmt nvarchar(max), @stmt1 nvarchar(max)select @stmt = isnull(@stmt + ', ', '') + name, @stmt1 = isnull(@stmt1 + ', ', '') + '(' + name + ')'from sys.columnswhere object_id = object_id('Table1') and name not in ('ID')select @stmt = 'select ID, ' + @stmt + ', (select sum(S.Q) from (values ' + @stmt1 + ') as S(Q) where S.Q is not null) as [Total] ' + 'from Table1 as T'exec sp_executesql @stmt = @stmtsql小提琴演示
#3
0
Expanding on Roman Pekar, If you're using a temp table and want to do this, you need to use tempdb like this:
扩展到Roman Pekar,如果你正在使用临时表并想要这样做,你需要像这样使用tempdb:
select@stmt = isnull(@stmt + ', ', '') + '[' + name + ']',@stmt1 = isnull(@stmt1 + ', ', '') + '(' + '[' + name + ']'+ ')'from tempdb.sys.columnswhere object_id = object_id('tempdb..##TempTable') and name not in ('ID')--ID would be one of the column names you DONT want to sum.--also notice the double pound sign. you need to declare your temp table with double pounds or it wont work--also notice how I put brackets around name, that's because my columns weren't working because they had slashes in their names.--the rest of the code is the sameselect @stmt = 'select Date_Packed, ' + @stmt + '' + ', (select sum(S.Q) from (values ' + @stmt1 + ') as S(Q) where S.Q is not null) as [Total] ' + 'from tempdb..##TempTableas T' print @stmtexec sp_executesql @stmt = @stmt--don't forget to drop it drop table ##TempTable