这样看行不? declare @tb table (FID int,值 nchar(1))insert into @tb select 0,'*' union all select 1,'红' union all select 2,'蓝' union all select 3,'绿 ' drop table 数据横表1 create table 数据横表1 (Fnum nchar(3),F1 int,F2 int) insert into 数据横表1 select '001',0,1 union all select '002',2,3 union all select '003',0,0 declare @strSql nvarchar(4000) declare @tablename nvarchar(20) set @strSql='' set @tablename='数据横表1' select @strSql=@strSql+'(select 值 from @tb where FID=t1.'+[name]+') as '+[name]+',' from syscolumns where id =( select id from sysobjects where xtype='u' and name=@tablename) --select ' select Fnum,'+left(@strSql,len(@strSql)-1)+' from '+@tablename+' as t1' exec (' select Fnum,'+left(@strSql,len(@strSql)-1)+' from '+@tablename+' as t1')
create table 基础表(FID int,值 varchar(5))insert 基础表 select 0,'*' union all select 1,'红' union all select 2,'蓝' union all select 3,'绿' create table tt(fnum char(3),f1 int,f2 int,f3 int) insert tt select '001',1,0,1 union all select '002',2,3,0create table tt1(fnum char(3),f1 int,f2 int) insert tt1 select '001',0,1 union all select '002',2,3 union all select '003',0,0DECLARE @SQL VARCHAR(8000),@table_name varchar(20) set @table_name='tt1' select @sql=isnull(@sql+',','')+'(select 值 from 基础表 where FID=a.['+name+']) as ['+name+']' from sys.syscolumns WHERE id=OBJECT_ID(@table_name) and name !='fnum'select @sql='select fnum,'+@sql+ 'from '+@table_name+' a'exec(@sql)
要求一个SQL语句,对不同数据库查询得出结果
(select 值 from 基础表 where FID=F1),
(select 值 from 基础表 where FID=F2)
from 数据横表1
declare @tb table (FID int,值 nchar(1))insert into @tb
select 0,'*' union all
select 1,'红' union all
select 2,'蓝' union all
select 3,'绿 ' drop table 数据横表1
create table 数据横表1 (Fnum nchar(3),F1 int,F2 int)
insert into 数据横表1
select '001',0,1 union all
select '002',2,3 union all
select '003',0,0 declare @strSql nvarchar(4000)
declare @tablename nvarchar(20)
set @strSql=''
set @tablename='数据横表1'
select @strSql=@strSql+'(select 值 from @tb where FID=t1.'+[name]+') as '+[name]+','
from syscolumns where id =(
select id from sysobjects
where xtype='u' and name=@tablename)
--select ' select Fnum,'+left(@strSql,len(@strSql)-1)+' from '+@tablename+' as t1'
exec (' select Fnum,'+left(@strSql,len(@strSql)-1)+' from '+@tablename+' as t1')
create table 基础表(FID int,值 varchar(5))insert 基础表
select 0,'*' union all
select 1,'红' union all
select 2,'蓝' union all
select 3,'绿' create table tt(fnum char(3),f1 int,f2 int,f3 int)
insert tt
select '001',1,0,1 union all
select '002',2,3,0create table tt1(fnum char(3),f1 int,f2 int)
insert tt1
select '001',0,1 union all
select '002',2,3 union all
select '003',0,0DECLARE @SQL VARCHAR(8000),@table_name varchar(20)
set @table_name='tt1'
select
@sql=isnull(@sql+',','')+'(select 值 from 基础表 where FID=a.['+name+']) as ['+name+']'
from sys.syscolumns WHERE id=OBJECT_ID(@table_name)
and name !='fnum'select @sql='select fnum,'+@sql+ 'from '+@table_name+' a'exec(@sql)
俺忽略了,,
fnum了