27,579
社区成员
发帖
与我相关
我的任务
分享
--哪有那么麻烦
DECLARE @T TABLE(
idyear INT,
YEARMONEY INT,
NUM0 INT,
NUM1 INT,
NUM2 INT,
NUM3 INT,
NUM4 INT,
NUM5 INT,
NUM6 INT
)
INSERT INTO @T
SELECT 2, 10, 0, 0, 10, 10, 8, 19, 30 UNION ALL
SELECT 5, 26, 0, 0, 0, 0, 0 , 26, 21 UNION ALL
SELECT 1, 7, 0, 7, 19, 13, 12, 13, 11
SELECT
SUM(CASE WHEN IDYEAR =0 THEN 0 ELSE NUM0 END)
,SUM(CASE WHEN IDYEAR =1 THEN 0 ELSE NUM1 END)
,SUM(CASE WHEN IDYEAR =2 THEN 0 ELSE NUM2 END)
,SUM(CASE WHEN IDYEAR =3 THEN 0 ELSE NUM3 END)
,SUM(CASE WHEN IDYEAR =4 THEN 0 ELSE NUM4 END)
,SUM(CASE WHEN IDYEAR =5 THEN 0 ELSE NUM5 END)
,SUM(CASE WHEN IDYEAR =6 THEN 0 ELSE NUM6 END)
FROM @T
/*
0 0 19 23 20 32 62
*/
-----------------------------------------
--> 测试时间:2009-07-25
--> 我的淘宝:http://shop36766744.taobao.com/
--------------------------------------------------
if object_id('[TB]') is not null drop table [TB]
create table [TB]([idyear] int,[yearmony] int,[num0] int,[num1] int,[num2] int,[num3] int,[num4] int,[num5] int,[num6] int)
insert [TB]
select 2,10,0,10,10,10,8,19,30 union all
select 5,26,0,0,0,0,26,13,21 union all
select 1,7,0,7,19,13,12,13,11
select * from [TB]
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select idyear=idyear'+',[num]='
+quotename(Name)+' from TB'
from syscolumns where ID=object_id('TB') and Name not in('idyear','yearmony')
order by Colid
exec('select idyear,总和=sum(num) from ('+@s+')t group by idyear order by [idyear]')--
/*
idyear 总和
----------- -----------
1 75
2 87
5 60
*/
drop table TB