--------如果隻要三列select [101(c)]=max([101(c)]) from 表 union all select [102(c)]=max([102(c)]) from 表 union all select [103(c)]=max([103(c)]) from 表
declare @sql varchar(8000) set @sql = 'select '''', 0' select @sql = @sql + ' union all select ''' + name + ''', max(' + name + ') from yourtable' from syscolumns where id = object_id('yourtable') exec( @sql )
select max(101(c)) as 101(c) from a union max(102(c)) as 102(c) from a union max(103(c)) as 103(c) from a
declare @str varchar(1000) set @str='' select @str=@str+' select '''+name+''', max('+name+') from test1 union all' from syscolumns where id=object_id('test1') set @str=left(@str,len(@str)-9) print @str exec( @str)
select [102(c)]=max([102(c)]) from 表 union all
select [103(c)]=max([103(c)]) from 表
set @sql = 'select '''', 0'
select @sql = @sql + ' union all select ''' + name + ''', max(' + name + ') from yourtable' from syscolumns where id = object_id('yourtable')
exec( @sql )
union max(102(c)) as 102(c) from a
union max(103(c)) as 103(c) from a
可能是101(C),也可能是102(BB)...
set @str=''
select @str=@str+' select '''+name+''', max('+name+') from test1 union all' from syscolumns where id=object_id('test1')
set @str=left(@str,len(@str)-9)
print @str
exec( @str)
不过由于列太多了,超过了varchar最大8000字符的限制,text也无法用于局部变量