日期:2014-05-18  浏览次数:20606 次

求取得一个表的所有字段名用逗号分割的函数
在oracle中 取得一个表的所有字段名用逗号分割用函数sys_connect_by_path来解决。
而在SQL中有相应的方法实现该功能吗?

------解决方案--------------------
SQL code
DECLARE @columns VARCHAR(500);
SET @columns='';

SELECT
    @column=@column+','+name
FROM sys.columns
WHERE [object_id]=OBJECT_ID(N'tbname');

SELECT STUFF(@column,1,1,'');

------解决方案--------------------
SQL code
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+name+',' from syscolumns where id=(select id from sysobjects  where name='tablename') order by colid
set @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 //r
return concat(",",($x/name/text())[1])') as varchar(8000)),1,1,'')