T-SQL, Dynamic aggregation query

Source: Internet
Author: User
Tags end insert sql 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)


Related Article

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.