例如：查询结果为
- SQL code
MemberID Name Sex Email Interest000001 张三 男 [email protected] 打球000001 张三 男 [email protected] 游泳000001 张三 男 [email protected] 看电影
我想把这些记录何为一条,如：
- SQL code
MemberID Name Sex Email Interest000001 张三 男 [email protected] 打球、游泳、看电影
怎么办？
------解决方案--------------------
- 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 @[email protected]+ 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 tbgocreate table tb( MemberID varchar(10), Name varchar(10), Sex varchar(10), Email varchar(20), Interest varchar(10))goinsert into tbselect '000001','张三','男',[email protected]','打球' union allselect '000001','张三','男',[email protected]','游泳' union allselect '000001','张三','男',[email protected]','看电影'goselect 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 MemberIDgo/*MemberID Name Sex Email Interest---------- ---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------------------000001 张三 男 [email protected] 打球,游泳,看电影(1 行受影响)*/