T-SQL, Dynamic aggregation query

Dynamic
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 (a) not null,faccnum INT not 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 Deposit ', 2) insert into accountmessage values (' 000002 ', ' Tianjin Deposit ', 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 ', ' 000001 ', 1,1000.00) inserts 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 of doing something

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

SELECT * Into #t from (select A.*,b.fbal to accountmessage a join accountbalance B on A.ffundcode=b.ffundcode and A.FACCN Um=b.faccnum) Tdeclare @SQL VARCHAR (8000) SET @SQL = ' Select Ffundcode ' Select @SQL = @SQL + ', sum (case when faccname = ' + t T + "' THEN fbal Else 0 End" [' +tt+ '] ' from (SELECT DISTINCT faccname as TT/#t) A SET @SQL = @SQL + ' from #t GROUP by Ffundcode ' EXEC (@SQL)