sql字段合并问题求解

daishunchaoaiqiu 2015-11-05 03:26:03
现有字段no,name
no name
1 jack
1 abc
2 aaa

怎么才能显示
1 jack,abc
2 aaa
根据no相同的合并成一条记录,求解。
...全文
125 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Yole 2015-11-05

CREATE TABLE #t_AQQ  
(ID INT,MYCON VARCHAR(20))  
INSERT INTO #t_AQQ  
SELECT '1','jack'  
UNION ALL  
SELECT '1','abc'  
UNION ALL  
SELECT '2','aaa'  


  
  
 SELECT t1.ID , 
 mycon=STUFF((SELECT ','+[mycon] FROM #t_AQQ t WHERE t.ID=t1.ID FOR XML PATH('')), 1, 1, '')  
FROM #t_AQQ t1
GROUP BY t1.ID
drop table #t_AQQ  
/*

(3 行受影响)
ID          mycon
----------- -------
1           jack,abc
2           aaa

(2 行受影响)
*/

回复
;with tb(no, name)
as
(
select 1   ,'jack' union all
select 1   ,'abc' union all
select 2   ,'aaa' 

)

select no,
       stuff(cast((select ','+ name from tb t2 where t1.no = t2.no for xml path('')) as varchar(2000)),1,1,'') as name
from tb t1
group by no
/*
no	name
1	jack,abc
2	aaa
*/
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-11-05 03:26
社区公告
暂无公告