IF exists (SELECT TABLE_NAME from Information_schema.tables where Table_Name = 'AccountMessage') Drop Table AccountMessagego
Create Table AccountMessage (Ffundcode Varchar (6) Not Null, FaccName Varchar (20) Not Null, FACCNUM INT NULL;
IF exists (SELECT TABLE_NAME from Information_Schema.tables Where Table_Name = 'AccountBalance "Drop Table AccountBalancego
Create Table AccountBalance (Ffundcode Varchar (6) Not Null, FaccNum Int Not Null, FDate DateTime Default (getdate ()) Not Null, Fbal Numeric (10, 2) Not null;
INSERT INTO AccountMessage Values ('000001', 'Beijing Deposit', 1) Insert Into AccountMessage Values ('000001', 'Shanghai Deposit', 2) Insert Into AccountMessage Values ('000001', 'Shenzhen Deposit', 3) Insert Into AccountMessage VALUES ( '000002', 'Beijing deposit', 1) INSERT INTO AccountMessage VALUES ( '000002', 'Shanghai deposits', 2) INSERT INTO AccountMessage VALUES ( '000002', 'Tianjin deposits', 3) INSERT INTO AccountMessage VALUES ('000003', 'Shanghai Deposit', 1) INSERT INTO AccountMessage Values ('000003', 'Fuzhou Deposit', 2)
INSERT INTO AccountBalance (FDATE, FFUNDCODE, FACCNUM, FBAL) VALUES ('2004-07-28) (' 2004-07-28) Insert Into AccountBalance (FDate, Ffundcode, Faccnum, FBAL) VALUES ('2004-07-28 ',' 000001 ', 2,1000.00) Insert Into AccountBalance (FDATE, FFUNDCODE, FACCNUM, FBAL) VALUES (' 2004-07-28 ',' 000001 ', 3,1120.00) Insert Into AccountBalance (fdate, ffundcode, FACCNUM, FBAL) VALUES ('2004-07-28', '000002', 1,2000.00) Insert Into AccountBalance (FDATE, FFUNDCODE, FACCNUM, FBAL) VALUES ('2004-07-28', '000002', 2, 1000.00) INSERT INTO AccountBalance (FDATE, FFUNDCODE, FACCNUM, FBAL) VALUES ('2004-07-28', '000002', 3, 1000.00) Insert Into AccountBalance (FDate, Ffundcode, Faccnum, Fbal) Values ('2004-07-28 ',' 000003 ', 1, 2000.00) Insert Into AccountBalance (FDATE, FFUNDCODE, FACCNUM, FBAL) VALUES (' 2004-07-28 ',' 000003 ', 2, 1000.00) GO two different ways
Declare @s nvarchar (4000) set @s = '' SELECT @ S = @ s ',' quotename (FACCNAME) '= Isnull (SUM (Case A.FACCNAME WHEN' Quotename (FaccName, '' ') 'then b.FBal end), 0)' from AccountMessage group by FAccNameexec ( 'select Fund Code =a.FFundCode' @s 'from AccountMessage a, AccountBalance bwhere a.FFundCode = b.FFundCode and a.FAccNum = b .Faccnumgroup by a.ffundcode ') Go
