T-SQL，动态聚合查询

领测软件测试网
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 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@#,@#北京存款@#,1)INSERT INTO AccountMessage VALUES(@#000001@#,@#上海存款@#,2)INSERT INTO AccountMessage VALUES(@#000001@#,@#深圳存款@#,3)INSERT INTO AccountMessage VALUES(@#000002@#,@#北京存款@#,1)INSERT INTO AccountMessage VALUES(@#000002@#,@#上海存款@#,2)INSERT INTO AccountMessage VALUES(@#000002@#,@#天津存款@#,3)INSERT INTO AccountMessage VALUES(@#000003@#,@#上海存款@#,1)INSERT INTO AccountMessage VALUES(@#000003@#,@#福州存款@#,2)

INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES (@#2004-07-28@#,@#000001@#,1,1000.00)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

两种不同的方法

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 基金代码=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 from AccountMessage a join AccountBalance b on a.ffundcode=b.ffundcode and a.faccnum=b.faccnum)tDECLARE @SQL VARCHAR(8000) SET @SQL=@#SELECT ffundcode@# SELECT @SQL= @SQL+ @#,sum(CASE WHEN FAccName = @#@#@# + tt + @#@#@# THEN FBal else 0 END) [@# +tt+ @#]@# FROM (SELECT DISTINCT FAccName as tt FROM #t) A SET @SQL=@SQL+@# FROM #t group by ffundcode@# exec (@SQL)

文章来源于领测软件测试网 https://www.ltesting.net/