如果可以多句,就可以动态得到,不需要写那么长: declare @ varchar(8000) set @='' select @=@+name+' from demo union all select ' from syscolumns where id=object_id('demo') set @=left(@,len(@)-16) exec('select '+@)
或: declare @ varchar(8000) set @='select ' select @=@+name+' from demo union all select ' from syscolumns where id=object_id('sysobjects') set @=left(@,len(@)-16) exec(@)
忘了别名:select col1 colName from demo union all select col2 from demo union all select col3 from demo ... union all select col15 from demo动态的话:declare @ varchar(8000) set @='' select @=@+name+' colName from demo union all select ' from syscolumns where id=object_id('demo') set @=left(@,len(@)-16) exec('select '+@)或:declare @ varchar(8000) set @='select ' select @=@+name+' colName from demo union all select ' from syscolumns where id=object_id('demo') set @=left(@,len(@)-16) exec(@)
一条语句 select col1 colName from demo union all select col2 from demo union all select col3 from demo ... union all select col15 from demo
如果确定就是15列,也可以这样啊:declare @i int,@s varchar(4000) set @s = 'Select col1 as colName from memo ' set @i = 2 while @i <= 15 begin select @s = @s + ' union all select col'+ltrim(cast(@i as char(2)))+' from memo ' set @i = @i+1 end Exec(@s)
declare @ varchar(8000)
set @=''
select @=@+name+' from demo union all select ' from syscolumns where id=object_id('demo')
set @=left(@,len(@)-16)
exec('select '+@)
declare @ varchar(8000)
set @='select '
select @=@+name+' from demo union all select ' from syscolumns where id=object_id('sysobjects')
set @=left(@,len(@)-16)
exec(@)
union all
select col2 from demo
union all
select col3 from demo
...
union all
select col15 from demo动态的话:declare @ varchar(8000)
set @=''
select @=@+name+' colName from demo union all select ' from syscolumns where id=object_id('demo')
set @=left(@,len(@)-16)
exec('select '+@)或:declare @ varchar(8000)
set @='select '
select @=@+name+' colName from demo union all select ' from syscolumns where id=object_id('demo')
set @=left(@,len(@)-16)
exec(@)
select col1 colName from demo
union all
select col2 from demo
union all
select col3 from demo
...
union all
select col15 from demo
set @s = 'Select col1 as colName from memo '
set @i = 2
while @i <= 15
begin
select @s = @s + ' union all select col'+ltrim(cast(@i as char(2)))+' from memo '
set @i = @i+1
end
Exec(@s)