T-SQL，动态聚合查询

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLES     WHERETABLE_NAME='AccountMessage')  DROPTABLEAccountMessageGO

CREATETABLEAccountMessage(FFundCodeVARCHAR(6)NOTNULL,FAccNameVARCHAR(20)NOTNULL,FAccNumINTNOTNULL);

IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLES     WHERETABLE_NAME='AccountBalance')  DROPTABLEAccountBalanceGO

CREATETABLEAccountBalance(FFundCodeVARCHAR(6)NOTNULL,FAccNumINTNOTNULL,FDateDATETIMEDEFAULT(getdate())NOTNULL,FBalNUMERIC(10,2)NOTNULL);

INSERTINTOAccountMessageVALUES('000001','北京存款',1)INSERTINTOAccountMessageVALUES('000001','上海存款',2)INSERTINTOAccountMessageVALUES('000001','深圳存款',3)INSERTINTOAccountMessageVALUES('000002','北京存款',1)INSERTINTOAccountMessageVALUES('000002','上海存款',2)INSERTINTOAccountMessageVALUES('000002','天津存款',3)INSERTINTOAccountMessageVALUES('000003','上海存款',1)INSERTINTOAccountMessageVALUES('000003','福州存款',2)

INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES('2004-07-28','000001',1,1000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES('2004-07-28','000001',2,1000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES('2004-07-28','000001',3,1120.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES('2004-07-28','000002',1,2000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES('2004-07-28','000002',2,1000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES('2004-07-28','000002',3,1000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES('2004-07-28','000003',1,2000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES('2004-07-28','000003',2,1000.00)go

两种不同的方法

declare@snvarchar(4000)set@s=''select@s=@s+','+quotename(FAccName) +'=isnull(sum(casea.FAccNamewhen'+quotename(FAccName,'''') +'thenb.FBalend),0)'fromAccountMessagegroupbyFAccNameexec('select基金代码=a.FFundCode'+@s+'fromAccountMessagea,AccountBalancebwherea.FFundCode=b.FFundCodeanda.FAccNum=b.FAccNumgroupbya.FFundCode')go

select*into#tfrom(selecta.*,b.fbalfromAccountMessageajoinAccountBalancebona.ffundcode=b.ffundcodeanda.faccnum=b.faccnum)tDECLARE@SQLVARCHAR(8000)SET@SQL='SELECTffundcode'SELECT@SQL=@SQL+',sum(CASEWHENFAccName='''+tt+'''THENFBalelse0END)['+tt+']'FROM(SELECTDISTINCTFAccNameasttFROM#t)ASET@SQL=@SQL+'FROM#tgroupbyffundcode'exec(@SQL)
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>