sql语句怎样将查询结果逗号分开

[股神]

select id from table

结果,
1
2
3
4
如何得到1，2，3，4
谢谢

[yangjiexi]

declare @string varchar(100)
set @string=''

select @string=@string+','+cast(id as varchar)
from table
order by id

select STUFF(@string,1,1,'')

[ojuju10]

declare @string varchar(100)

select @string=Isnull(@string,'')+ltrim(id)+','
from table
order by id

select left(@string,len(@string)-1)

[dawugui]

select col from table

结果集：
1
2
3
4
5

我想要的是用逗号隔开的一个字符串
1,2,3,4,5

最好是用SQL语句来实现。。。我不想用游标来实现。。。
100分奉上。谢谢。。


declare @output varchar(8000)
select @output = coalesce(@output+',', '') + col from table
print @output



--如果col是数值型要转换一下：
declare @output varchar(8000)
select @output = coalesce(@output+',', '') + cast(col as varchar) from table
print @output

declare @output varchar(8000)

--输出系统表sysobjects的colid
select @output = coalesce(@output+',', '') + cast(colid as varchar) from syscolumns where id = object_id('sysobjects') order by colid
print @output
/*
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
*/

--输出系统表sysobjects的列名
set @output=null
select @output = coalesce(@output+',', '') + name from syscolumns where id = object_id('sysobjects') order by colid
print @output
/*
name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltrig,instrig,updtrig,seltrig,category,cache
*/


create table tb(id varchar(10),username varchar(10))
insert into tb values('1',      '123')
insert into tb values('2',      '234')
insert into tb values('3',      'hell')
go

declare @a varchar(5000),@b  varchar(5000)
select @a='',@b=''
select @a=@a+'，'+rtrim(id),@b=@b+'，'+username from tb
select 'id',stuff(@a,1,1,'') union select 'username',stuff(@b,1,1,'') 


drop table tb

/*
-------- --------------
id       1，2，3
username 123，234，hell

（所影响的行数为 2 行）
*/

[fa_ge]

declare @string varchar(100)
set @string=''

select @string=@string+','+cast(id as varchar)
from table
order by id

select STUFF(@string,1,1,'')

[andysun88]

declare @strValue varchar(max)
set @strValue =''
select @strValue = @strValue + Convert(varchar,id)+',' from [table]
select substring(@strValue,0,len(@strValue)) as Ids

測試結果
      ids
1   1,2,3,4

[yinqi025]

这就是行转列，就必须建一个函数了
还有你的那个没办法实现，只能与一个列来进行聚合才行

--外部配置
if exists(select 1 from sysobjects where name='selectGroup')
begin
drop table selectGroup
end
go
--创建表
create table selectGroup(
id int,
name varchar(50)
)
go

--创建函数
create function dbo.RowsToColumn(@Column varchar(100))
returns varchar(1000)
as
begin
declare @tempString varchar(1000)
set @tempString=''
select @tempString=@tempString+','+cast(id as varchar)
from selectGroup
where [name]=@Column
RETURN(stuff(@tempString,1,1,''))
end
go

--测试数据
insert selectGroup select 1,'b'
union all select 2,'a'
union all select 3,'a'
union all select 4,'a'
go

--测试数据
select [name],dbo.RowsToColumn([name]) as 结果 from selectGroup
group by [name]


--消毁
drop table selectGroup
drop function dbo.RowsToColumn

[chuifengde]

declare @a varchar(100)
select @a=isnull(@a+',','')+ltrim(id) from table order by id


select @a

[wzy_love_sly]

drop table tb
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO
drop function f_str
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @re varchar(100)
    SET @re=''
    SELECT @re=@re+','+CAST(col2 as varchar)
    FROM tb
    WHERE col1=@col1
    RETURN(STUFF(@re,1,1,''))
END
GO

--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1

col1 col2
a 1,2
b 1,2,3