求SQL 语句

[浩子]

原表中的内容：
KindID Memo
1 1111
1 2222
2 3333
2 4444
2 5555

要求的查询结果为：
KindID Memo1
1 1111,2222
2 3333,4444,5555

望知道的兄弟们多多指教！

[jinjazz]

--建立测试环境
Create Table 表(KindID varchar(10),Memo varchar(10))
--插入数据
insert into 表
select '1','1111' union
select '1','2222' union
select '2','3333' union
select '2','4444' union
select '2','5555'
--测试语句
go
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+Memo FROM 表 WHERE KindID=@vchA
RETURN(substring(@r,2,8000))
END
GO

select KindID,dbo.FunMergeCharField(KindID) from 表 group by KindID
--删除测试环境
Drop FUNCTION FunMergeCharField
Drop Table 表

/*

KindID
---------- --------------------------
2 3333,4444,5555
*/

[wyb0026]

潮的一个例子
--建立测试环境
Create Table 表(CA varchar(10),CB varchar(10))
--插入数据
insert into 表
select '1','a' union
select '1','b' union
select '2','a' union
select '2','b' union
select '2','c' union
select '3','d' union
select '3','e'
select * from 表
--测试语句
go
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+CB FROM 表 WHERE CA=@vchA
RETURN(substring(@r,2,8000))
END
GO
select ca,dbo.FunMergeCharField(ca)as cb from 表 group by ca
--删除测试环境
Drop Table 表
drop FUNCTION FunMergeCharField


/*
ca cb
---------- ----------
1 a,b
2 a,b,c
3 d,e
*/

[xiaomeixiang]

declare @tb table(KindID varchar(100),Memo varchar(100))
--插入数据
insert into @tb
select '1','1111' union
select '1','2222' union
select '2','3333' union
select '2','4444' union
select '2','5555'

declare @KindID varchar(100),@Memo varchar(100)
select @KindID='',@Memo=''
update @tb set @memo=case when kindid=@kindid then @memo+','+memo else memo end,
@kindid=kindid,memo=@memo
select kindid,max(memo) as meno from @tb group by kindid

[zjcxc]

--建立测试环境
Create Table 表(KindID varchar(10),Memo varchar(10))
--插入数据
insert into 表
select '1','1111' union
select '1','2222' union
select '2','3333' union
select '2','4444' union
select '2','5555'
go

select KindID,Memo=cast(Memo as varchar(8000)) into # from 表 order by KindID
declare @KindID varchar(10),@Memo varchar(8000)
update # set @Memo=case when @KindID=KindID then @Memo+','+Memo else Memo end
,memo=@memo,@KindID=KindID
select KindID,Memo=max(Memo) from # group by KindID
drop table #
go

--删除测试环境
Drop Table 表

[浩子]

谢谢大家，大家的结果应该都正确，但不知只用一条SQL语句能否实现？