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

在线等~~~~~请教一个高难度SQL语句问题,各个大哥帮帮忙吧
create table test
(
id int identity(1,1),
name varchar(10),
)
go
insert into test values('A')
insert into test values('B')
insert into test values('C')


我想通过一条SQL语句,查询TEST表,想得到如下字符串:“A,B,C” 。请问如何实现?

------解决方案--------------------
SQL code

select stuff((select ','+name from test for xml path('')),1,1,'')

------解决方案--------------------
declare @V varchar(8000)
set @V = ''
select @V = @V + ',' + name from test where id in (1,2,3);
set @V = substring(@V,len(@V) -1);
select @V
------解决方案--------------------
SQL code

create table test
(
id int identity(1,1),
name varchar(10),
)
go
insert into test values('A')
insert into test values('B')
insert into test values('C')
go

declare @str varchar(1000)

select @str = isnull(@str+',','')+name from test
print @str

drop table test

/*****************

A,B,C

------解决方案--------------------
SQL code

create table test
(
id int identity(1,1),
name varchar(10),
)

insert into test values('A')
insert into test values('B')
insert into test values('C')


select stuff((select ','+name from test for xml path('')),1,1,'') name

name
--------------------
A,B,C

(1 row(s) affected)

------解决方案--------------------
MS-SQL SERVER单列合并的四种常用方法--【叶子】
------解决方案--------------------
方法2,
SQL code

create table test
(
id int identity(1,1),
name varchar(10),
)

insert into test values('A')
insert into test values('B')
insert into test values('C')


declare @names varchar(100)=''
select @names=@names+','+name from test
select stuff(@names,1,1,'') name

name
--------------------
A,B,C

(1 row(s) affected)