日期:2014-05-18 浏览次数:20730 次
MemberID Name Sex Email Interest 000001 张三 男 aaa@hao.com 打球 000001 张三 男 aaa@hao.com 游泳 000001 张三 男 bbb@hao.com 看电影
MemberID Name Sex Email Interest 000001 张三 男 aaa@hao.com 打球、游泳、看电影
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 行受影响)
------解决方案--------------------
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 行受影响) */