请帮帮忙~~~

[TTLOVEYOU3344]

select colum1 from table a　假如查出来的数据是如下
1
2
3
4
5
6
－－－－－－－－－－－－－－－－－－－－－－－
如果我想显示成一行，中间用逗号隔开该怎么做呢？效果如下：1，2，3，4，5，6
由于这个是子查询，希望给的方法越简单越好，在线等待中

[子陌红尘]

declare @t table(column1 int)
insert into @t select 1
insert into @t select 2
insert into @t select 3
insert into @t select 4
insert into @t select 5
insert into @t select 6


declare @str varchar(8000)
select @str=isnull(@str,'')+','+rtrim(colum1) from @t
select stuff(@str,1,1,'')

[子陌红尘]

declare @t table(column1 int)
insert into @t select 1
insert into @t select 2
insert into @t select 3
insert into @t select 4
insert into @t select 5
insert into @t select 6


declare @str varchar(8000)
select @str=isnull(@str,'')+','+rtrim(column1) from @t
select stuff(@str,1,1,'')


/*
--------------------
1,2,3,4,5,6
*/

[wangdehao]

declare @t table(colum1 int)

insert @t select 1
union select 2
union select 3
union select 4
union select 5
union select 6

DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(colum1 as varchar)
FROM @t
select stuff(@re,1,1,'')

[TTLOVEYOU3344]

有没有更简单的方法，因为我这个是放在子查询里面！不是独立的小功能，我希望最好是一条查询语句，这样我才能把它作为一个子查询放到查询语句里去

[TTLOVEYOU3344]

感谢libin_ftsafe(子陌红尘)，感谢 wangdehao(找找找)两位给的代码，让我受益匪浅！

[子陌红尘]

--那就用函数吧:

--生成测试数据
create table t(colum1 int)
insert into t select 1
insert into t select 2
insert into t select 3
insert into t select 4
insert into t select 5
insert into t select 6
go

--创建用户定义函数
create function f_str()
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=isnull(@str,'')+','+rtrim(colum1) from t
set @str=stuff(@str,1,1,'')
return @str
end
go

--执行查询调用
select dbo.f_str() as colum1
go

/*
colum1
-----------------
1,2,3,4,5,6
*/


--删除测试数据
drop function f_str
drop table t
go

[$扫地僧$]

create table A
(
id int
)
insert into A select 1
insert into A select 2
insert into A select 3
insert into A select 4
insert into A select 5
insert into A select 6

create function T_Fun()
returns @T table( T varchar(8000))
as
begin
declare @T_SQL varchar(8000)
set @T_SQL=''
select @T_SQL=@T_SQL+','+cast(id as varchar) from A
insert @T select stuff(@T_SQL,1,1,'')
return
end

select * from dbo.T_Fun()

[ReViSion]

哈哈，听高人讲课

[TTLOVEYOU3344]

libin_ftsafe(子陌红尘)兄，真不知道该怎么谢你，太感谢了，有机会一定请你吃饭！！

[常飞梦]

declare @t table(col varchar(10))
insert @t
select '1' union all
select '2' union all
select '3' union all
select '4' union all
select '5' union all
select '6'

declare @str varchar(8000)
set @str=''
select @str=@str+','+col from @t
select stuff(@str,1,1,'')

---看来这个例子真是精典，仍是有这么多人问这个问题!

[Well]

樓上的方法好