clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 984296 fiddles created (11687 in the last week).

CREATE TABLE AmountTable ([PartNum] varchar(20),[a] int, [AM01] float,[AM02] float,[AM03] float,[AM04] float,[AM05] float,[AM06] float, [AM07] float,[AM08] float,[AM09] float,[AM10] float,[AM11] float,[AM12] float);
 hidden batch(es)


INSERT INTO AmountTable ([PartNum],[a],[AM01],[AM02],[AM03],[AM04],[AM05],[AM06],[AM07],[AM08],[AM09], [AM10],[AM11],[AM12]) VALUES ('aa',4,1,2,3,4,5,6,7,8,9,10,11,12), ('aa',4,2,4,6,8,10,12,14,16,18,20,22,24), ('aa',5,1,2,3,4,5,6,7,8,9,10,11,12), ('aa',5,2,4,6,8,10,12,14,16,18,20,22,24), ('aa',5,3,6,9,12,15,18,21,24,27,30,33,36), ('bb',4,10,20,30,40,50,60,70,80,90,100,110,120), ('bb',4,20,40,60,80,100,120,140,160,180,200,220,240), ('bb',5,10,20,30,40,50,60,70,80,90,100,110,120), ('bb',5,20,40,60,80,100,120,140,160,180,200,220,240), ('bb',5,30,60,90,120,150,180,210,240,270,300,330,360);
10 rows affected
 hidden batch(es)


select * from AmountTable
PartNum a AM01 AM02 AM03 AM04 AM05 AM06 AM07 AM08 AM09 AM10 AM11 AM12
aa 4 1 2 3 4 5 6 7 8 9 10 11 12
aa 4 2 4 6 8 10 12 14 16 18 20 22 24
aa 5 1 2 3 4 5 6 7 8 9 10 11 12
aa 5 2 4 6 8 10 12 14 16 18 20 22 24
aa 5 3 6 9 12 15 18 21 24 27 30 33 36
bb 4 10 20 30 40 50 60 70 80 90 100 110 120
bb 4 20 40 60 80 100 120 140 160 180 200 220 240
bb 5 10 20 30 40 50 60 70 80 90 100 110 120
bb 5 20 40 60 80 100 120 140 160 180 200 220 240
bb 5 30 60 90 120 150 180 210 240 270 300 330 360
 hidden batch(es)


select PartNum,a,sum(Total) as Total_In from ( select PartNum,a,1 as TempMonth,AM01 as Total from AmountTable union all select PartNum,a,2 as TempMonth,AM02 as Total from AmountTable union all select PartNum,a,3 as TempMonth,AM03 as Total from AmountTable union all select PartNum,a,4 as TempMonth,AM04 as Total from AmountTable union all select PartNum,a,5 as TempMonth,AM05 as Total from AmountTable union all select PartNum,a,6 as TempMonth,AM06 as Total from AmountTable union all select PartNum,a,7 as TempMonth,AM07 as Total from AmountTable union all select PartNum,a,8 as TempMonth,AM08 as Total from AmountTable union all select PartNum,a,9 as TempMonth,AM09 as Total from AmountTable union all select PartNum,a,10 as TempMonth,AM10 as Total from AmountTable union all select PartNum,a,11 as TempMonth,AM11 as Total from AmountTable union all select PartNum,a,12 as TempMonth,AM12 as Total from AmountTable ) as TempTable where 1=1 and a=4 -- for 4 月 and TempMonth<=4 group by PartNum,a
PartNum a Total_In
aa 4 30
bb 4 300
 hidden batch(es)


select PartNum,a,sum(Total) as Total_Out from ( select PartNum,a,1 as TempMonth,AM01 as Total from AmountTable union all select PartNum,a,2 as TempMonth,AM02 as Total from AmountTable union all select PartNum,a,3 as TempMonth,AM03 as Total from AmountTable union all select PartNum,a,4 as TempMonth,AM04 as Total from AmountTable union all select PartNum,a,5 as TempMonth,AM05 as Total from AmountTable union all select PartNum,a,6 as TempMonth,AM06 as Total from AmountTable union all select PartNum,a,7 as TempMonth,AM07 as Total from AmountTable union all select PartNum,a,8 as TempMonth,AM08 as Total from AmountTable union all select PartNum,a,9 as TempMonth,AM09 as Total from AmountTable union all select PartNum,a,10 as TempMonth,AM10 as Total from AmountTable union all select PartNum,a,11 as TempMonth,AM11 as Total from AmountTable union all select PartNum,a,12 as TempMonth,AM12 as Total from AmountTable ) as TempTable where 1=1 and a=5 -- for 4 月 and TempMonth<=4 group by PartNum,a
PartNum a Total_Out
aa 5 60
bb 5 600
 hidden batch(es)


select distinct AmountTable.PartNum,isnull(Table_In.Total_In,0) as Total_In,isnull(Table_Out.Total_Out,0) as Total_Out from AmountTable -- for 收入 left join ( select PartNum,a,sum(Total) as Total_In from ( select PartNum,a,1 as TempMonth,AM01 as Total from AmountTable union all select PartNum,a,2 as TempMonth,AM02 as Total from AmountTable union all select PartNum,a,3 as TempMonth,AM03 as Total from AmountTable union all select PartNum,a,4 as TempMonth,AM04 as Total from AmountTable union all select PartNum,a,5 as TempMonth,AM05 as Total from AmountTable union all select PartNum,a,6 as TempMonth,AM06 as Total from AmountTable union all select PartNum,a,7 as TempMonth,AM07 as Total from AmountTable union all select PartNum,a,8 as TempMonth,AM08 as Total from AmountTable union all select PartNum,a,9 as TempMonth,AM09 as Total from AmountTable union all select PartNum,a,10 as TempMonth,AM10 as Total from AmountTable union all select PartNum,a,11 as TempMonth,AM11 as Total from AmountTable union all select PartNum,a,12 as TempMonth,AM12 as Total from AmountTable ) as TempTable where 1=1 and a=4 -- for 4 月 and TempMonth<=4 group by PartNum,a ) as Table_In on AmountTable.PartNum=Table_In.PartNum -- for 支出 left join ( select PartNum,a,sum(Total) as Total_Out from ( select PartNum,a,1 as TempMonth,AM01 as Total from AmountTable union all select PartNum,a,2 as TempMonth,AM02 as Total from AmountTable union all select PartNum,a,3 as TempMonth,AM03 as Total from AmountTable union all select PartNum,a,4 as TempMonth,AM04 as Total from AmountTable union all select PartNum,a,5 as TempMonth,AM05 as Total from AmountTable union all select PartNum,a,6 as TempMonth,AM06 as Total from AmountTable union all select PartNum,a,7 as TempMonth,AM07 as Total from AmountTable union all select PartNum,a,8 as TempMonth,AM08 as Total from AmountTable union all select PartNum,a,9 as TempMonth,AM09 as Total from AmountTable union all select PartNum,a,10 as TempMonth,AM10 as Total from AmountTable union all select PartNum,a,11 as TempMonth,AM11 as Total from AmountTable union all select PartNum,a,12 as TempMonth,AM12 as Total from AmountTable ) as TempTable where 1=1 and a=5 -- for 4 月 and TempMonth<=4 group by PartNum,a ) as Table_Out on AmountTable.PartNum=Table_Out.PartNum where 1=1 order by AmountTable.PartNum
PartNum Total_In Total_Out
aa 30 60
bb 300 600
 hidden batch(es)