怎样合并表中的字段

[yaganblw]

ID CID Desc
1 2 西瓜
2 3 萝卜
3 3 芹菜
4 2 苹果
5 1 豆腐
6 2 葡萄

需求：我要获得所有CID=2的水果
数据在一处显示，逗号隔开，如我喜欢的水果有：西瓜，苹果，葡萄

2012最后一天，大牛给力。

[yaganblw]

是说要用到游标吗？

[ChinaITOldMan]

cursor is ok for this issue

[知识青年]

--> 测试数据：#tb IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([ID] INT,[CID] INT,[Desc] VARCHAR(4)) INSERT #tb SELECT 1,2,'西瓜' UNION ALL SELECT 2,3,'萝卜' UNION ALL SELECT 3,3,'芹菜' UNION ALL SELECT 4,2,'苹果' UNION ALL SELECT 5,1,'豆腐' UNION ALL SELECT 6,2,'葡萄' --------------开始查询-------------------------- SELECT [CID],STUFF((SELECT ','+[Desc] FROM #tb WHERE [CID]=a.[CID] FOR XML PATH('')),1,1,'') FROM #tb AS a GROUP BY [CID] ----------------结果---------------------------- /* CID (无列名) 1 豆腐 2 西瓜,苹果,葡萄 3 萝卜,芹菜 */

[szm341]

select cid,stuff((select ','+[desc] from table where a.cid=cid for xml path('')),1,1,'')
from table as a where cid=2
group by cid

适用sql2005及以上版本

[波西米亚人生]

常用 的一般都是通过游标处理

[波西米亚人生]

用pivot也可以实现 http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

[骑着蜗牛去爬山]

select stuff((select ','+[desc] from tb where cid=2 for xml path('')),1,1,'')

[naochen]

弄的好复杂,在前台处理就很方便

[longai123]

select b.[cid],left(StuList,len(StuList)-1)as tb from (
SELECT [cid],
(SELECT [desc]+',' FROM tb 
  WHERE [cid]=a.[cid] 
  FOR XML PATH('')) AS StuList
FROM tb A 
GROUP BY cid
) B 

[习惯性蹭分]

if OBJECT_ID('test')is not null
drop table test
go 
create table test(id int,clid int,[desc] varchar(20))
insert into test
select 1,    2,    '西瓜' union all
select 2,    3,    '萝卜' union all
select 3,    3,    '芹菜' union all
select 4,    2,   '苹果' union all
select 5,    1,    '豆腐' union all
select 6,    2,   '葡萄' 
declare @val varchar(max)
select @val=ISNULL(@val+',','')+[desc] from test where clid=2
select @val

[dodolzc10]

3楼正解!！

[ljr_aa]

1、在前台处理好，以后查询就快很多。 2、可以用自定义函数。 3、查询语句 --sql2005 SELECT clid,STUFF((SELECT ','+[Desc] FROM ##test WHERE clid=a.clid FOR XML PATH('')),1,1,'') FROM ##test AS a GROUP BY clid --sql2000 Declare @Deli VarChar(1) Set @deli='; ' Declare @result varchar(1000) select @result=isnull(@result+@deli,'')+rtrim([Desc]) from ##test where clid='2' group by [Desc] select @result

[yaganblw]