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

我想把查询的记录合为一条怎么办?
例如:查询结果为
SQL code

MemberID      Name      Sex      Email          Interest
000001        张三       男      aaa@hao.com     打球
000001        张三       男      aaa@hao.com     游泳
000001        张三       男      bbb@hao.com     看电影


我想把这些记录何为一条,如:
SQL code

MemberID      Name      Sex      Email          Interest
000001        张三       男      aaa@hao.com     打球、游泳、看电影


怎么办?

------解决方案--------------------
SQL code
create table T1(
code varchar(50),
[name] varchar(50),
dec varchar(50)

)

create table T2
(
code varchar(50),
mark varchar(50)

)

insert into T1 select 
'1002',' 内存1',' 测试1' 

insert into T2 values
('1002' ,'备注1001' )  
insert into T2 values('1002' ,'备注1002v88'  )
insert into T2 values('1003 ','备注1002v55'  )
insert into T2 values('1002' ,'备注1002v1 '  ) 
insert into T2 values('1006' ,'备注1006' )
 
 

select T1.code,T1.name,T1.dec,REPLACE((select ','+mark  from T1 for xml path('')),',备注','') from T1 left join T2 ON T1.code=T2.code where T2.mark ='备注1001'
 
  
  
  declare @strs varchar(100)
  set @strs='';
 select  @strs=@strs+ mark from T2
 
 select T1.code,T1.name,T1.dec, replace (@strs,'备注','') from T1  
  
  
  
  
  
  
  
  code                                               name                                               dec                                                
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------
1002                                                内存1                                                测试1                                               1001

(1 行受影响)

code                                               name                                               dec                                                
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------
1002                                                内存1                                                测试1                                               10011002v881002v551002v1 1006

(1 行受影响)

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

if object_id('tb') is not null
   drop table tb
go
create table tb
(
 MemberID varchar(10),
 Name varchar(10),
 Sex varchar(10),
 Email varchar(20),
 Interest varchar(10)
)
go
insert into tb
select '000001','张三','男','aaa@hao.com','打球' union all
select '000001','张三','男','aaa@hao.com','游泳' union all
select '000001','张三','男','aaa@hao.com','看电影'
go
select MemberID,Name=Max(Name),Sex=Max(Sex),Email=Max(Email),
 Interest=stuff((select ','+Interest from tb where MemberID=a.MemberID for xml path('')),1,1,'')
 from tb a group by MemberID
go
/*
MemberID   Name       Sex        Email                Interest
---------- ---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------------------
000001     张三         男          aaa@hao.com          打球,游泳,看电影

(1 行受影响)
*/