在oracle中 取得一个表的所有字段名用逗号分割用函数sys_connect_by_path来解决。
而在SQL中有相应的方法实现该功能吗？
------解决方案--------------------
- SQL code
DECLARE @columns VARCHAR(500);SET @columns='';SELECT @[email protected]+','+nameFROM sys.columnsWHERE [object_id]=OBJECT_ID(N'tbname');SELECT STUFF(@column,1,1,'');
------解决方案--------------------
- SQL code
declare @sql varchar(1000)set @sql=''select @[email protected]+name+',' from syscolumns where id=(select id from sysobjects where name='tablename') order by colidset @sql=left(@sql,len(@sql)-1)print @sql
------解决方案--------------------
- SQL code
DECLARE @columns VARCHAR(500)select @columns=isnull(@columns+',','')+name from syscolumns where id=object_id('表名')select @columns
------解决方案--------------------
- SQL code
Create table tb(id int,name varchar(50),age int)select stuff(cast((select name from syscolumns where id=object_id('tb')for xml path('r'),root('root'),type).query('for $x in //rreturn concat(",",($x/name/text())[1])') as varchar(8000)),1,1,'')