22,199
社区成员
发帖
与我相关
我的任务
分享
declare @colname varchar(50)
select @colname=select syscolumns.name
from syscolumns
where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName'
exec('select '+@colname+ ' from table1')
set nocount on
if object_id('tb') is not null drop table tb
go
create table tb(id int , bool1 varchar(10), bool2 varchar(10), bool3 varchar(10))
insert tb select 1, 'true', 'false', 'false'
insert tb select 2, 'false', 'true', 'flase'
insert tb select 3, 'true', 'false', 'false'
declare @sql varchar(8000)
--set @sql='select * from ('
select @sql = isnull(@sql + ' union all ' , '' ) + ' select id , [booltrue] = case when ' + quotename(Name , '') + '=''true'' then'+quotename(Name , '''')+ ' end from tb '
from syscolumns
where name!=N'ID' and id=object_id('tb')
order by colid asc
set @sql='select * from (' +@sql +') t where booltrue is not null'
exec(@sql + ' order by id ')
/*id booltrue
----------- --------
1 bool1
2 bool2
3 bool1
*/
--更正
--引入中间变量
declare @colname varchar(50)
select @colname= syscolumns.name
from syscolumns
where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName'
exec('select '+@colname+ ' from table1')
--引入中间变量
declare @colname varchar(50)
select @colname=select syscolumns.name
from syscolumns
where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName'
exec('select '+@colname+ ' from table1')