SQL查询

[andy20066]

表结构：字段1 字段2
1 a
1 b
1 c


我想要字段2的值合起来放一个字段里（abc）

如何做呢？谢谢

[maguizhong]

--> 数据库版本:
--> Microsoft SQL Server 2008 R2 - 10.50.1600
--> 测试数据：[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO

---->建表
create table [TB]([Id] int,[name] varchar(3))
insert [TB] values(1,'a'),(2,'b'),(3,'c')
GO

declare @s varchar(1000)
select @s=isnull(@s,'') + name from TB
select @s
GO

select ''+name from TB for xml path('')
GO

[华夏小卒]

declare @s varchar(1000)

select @s=isnull(@s,'') +字段2 from tb

select @s

[AcHerat]

create table a(id int,ar varchar(10))
insert into a values('1','a')
insert into a values('1','b')
insert into a values('1','c')
go

select t.id,
        stuff((select ',' + ar from a where id = t.id for xml path('')),1,1,'')ar 
from a t
group by t.id

drop table a

id          ar
----------- --------
1           a,b,c

(1 行受影响)

[水族杰纶]

select 字段1,
       字段2=(select ''+字段2
              from tb
              where 字段1=t.字段1
              for xml path(''))
from tb t

[Starting6525]

create table d(id int,ar varchar(10))
insert into d values('1','a')
insert into d values('1','b')
insert into d values('1','c')
insert into d values('2','E')
insert into d values('2','F')
insert into d values('3','G')
go

SELECT DISTINCT ID,SUBSTRING(AR,1,LEN(AR)-1) FROM(
select ID, AR=(select AR+',' from D where ID = t.ID for xml path(''))
from D AS T) AS B
GO