1楼手误.select 'ID' as ID,'Name' as name,'Password' as password union all select id,name,password from [Admin]
declare @sql varchar(8000) declare @sql1 varchar(8000) set @sql='select ' set @sql1='select ' select @sql=@sql+''''+name+''''+',' from syscolumns where id=object_id('ADMIN') set @sql =left(@sql ,len(@sql)-1) + ' union all ' select @sql1=@sql1+'cast( '+name+' as varchar) ,' from syscolumns where id=object_id('ADMIN') print @sql1 set @sql =@sql+left(@sql1 ,len(@sql1)-1)+ ' from mesel00h ' EXEC(@sql)
谢谢大家。我想要一个通用的方法,如输入一个表名,得到那种结果。而不是针对某个具体表。 像’熟不了的木瓜‘那样,不过你给的代码调试不成功,另外请问, set @sql =@sql+left(@sql1 ,len(@sql1)-1)+ ' from mesel00h ' 中的 mesel00h 是系统表吗?
declare @col varchar(100) set @col='' select @col=@col+''''+name+''''+name+',' from syscolumns where id=object_id('admin') set @col=left(@col,len(@col)-1) exec('select '+@col+' union all select ltrim(id),name,password from admin')
union all
select id,name,password
from [Admin]
declare @sql1 varchar(8000)
set @sql='select '
set @sql1='select '
select @sql=@sql+''''+name+''''+',' from syscolumns where id=object_id('ADMIN')
set @sql =left(@sql ,len(@sql)-1) + ' union all '
select @sql1=@sql1+'cast( '+name+' as varchar) ,' from syscolumns where id=object_id('ADMIN')
print @sql1
set @sql =@sql+left(@sql1 ,len(@sql1)-1)+ ' from mesel00h '
EXEC(@sql)
像’熟不了的木瓜‘那样,不过你给的代码调试不成功,另外请问,
set @sql =@sql+left(@sql1 ,len(@sql1)-1)+ ' from mesel00h '
中的 mesel00h 是系统表吗?
set @col=''
select @col=@col+''''+name+''''+name+',' from syscolumns where id=object_id('admin')
set @col=left(@col,len(@col)-1)
exec('select '+@col+' union all select ltrim(id),name,password from admin')
对于datetime好办,关联systypes表,能够转换成正确的格式;
对于binary/varbinary/timestamp类型也好办,有个系统函数可以转换成字符串
对于image/text/ntext,基本上是没法处理。