table中存储着其他表的名称，通过select表table同时调出其他表的信息，如： table A B table_1 a table_2 b table_1 A 1 table_2 A 2 结果要求得到table_1和table_2中A字段的最大值 table.B C a 1 b 2
--示例数据 create table [table](A sysname,B varchar(10)) insert [table] select 'table_1','a' union all select 'table_2','b' create table table_1(A int) insert table_1 select 1 union all select 2 create table table_2(A int) insert table_2 select 3 union all select 4 go --动态sql declare @s varchar(8000) set @s='' select @s=@s+' when '''+A+''' then(select max(A) from ['+A+'])' from [table] group by A exec('select B,C=case A'+ @s +' end from [table]') go --删除测试 drop table [table],table_1,table_2 /*--测试结果 B C ---------- ----------- a 2 b 4 --*/
OtherDemo：表APList存在其他表的EntityID和ID，DataEntity:实体表，获取Billnodeclare @s varchar(8000)set @s = ''select @s = @s + ' when ID = ''' + Convert(varchar(4),a.ID) + ''' then (select billno from ['+ b.TableName + '] where ID = '+ Convert(varchar(4),RefID) + ')' from ARList a left join DataEntity b on a.EntityID = b.EntityIDselect @sexec('select ID,SourceObject = case '+ @s + ' else '' '' end from ARlist')Ps:可能长度不够用
declare @s varchar(8000)set @s = ''select @s = @s + 'when EntityID = ''' + Convert(varchar(12),a.EntityID) + ''' then (select billno from ['+ b.TableName + '] where ID = RefID) ' from (select Distinct entityID from ARList) a left join DataEntity b on a.EntityID = b.EntityID select @sexec('select ID,SourceObject = case '+ @s + ' else '' '' end from ARlist')Ps:改变语句即可(要判断@s长度是否大于0，也许是空数据呢)
.: 
