表
id iID content
1 100 a
2 100 b
3 101 c
4 101 d
5 101 e
. . .
. . .
. . .
要求实现这样的结果
iID content
100 a,b
101 c,d,e
. .
. .
. .
------解决方案--------------------------------------------------------
- SQL code
--创建环境create table test(iID int,content varchar(100))insert into test select 100,'a' union select 100,'b' union select 101,'c' union select 101,'d' union select 101,'e'--创建一个合并的函数create function contentDo(@id int)returns varchar(8000)asbegindeclare @str varchar(8000)set @str=''select @str=@str+','+cast(content as varchar) from test where iID=@id set @str=right(@str,len(@str)-1)return(@str)Endgo--调用自定义函数得到结果select iID,max(dbo.contentDo(iID)) as content from test group by iID--查询结果iID content ------------100 a,b 101 c,d,e