--我只知道利用函数,期待其他方式CREATE FUNCTION dbo.GetString (@seqno VARCHAR(10)) RETURNS VARCHAR(50) AS BEGIN DECLARE @Str AS VARCHAR(50) SET @Str = '' SELECT @Str = @Str + ',' + RTRIM(fdi) FROM tb WHERE seqno = @seqno SET @Str = SUBSTRING(@Str, 2, LEN(@Str)) RETURN @Str END--调用 SELECT seqno, dbo.GetString(seqno) AS fdi FROM tb GROUP BY seqno
借助临时表,在SQL7.0中比较合适:create table aa(id int identity(1,1),seqno varchar(15),fdi varchar(20)) insert aa(seqno,fdi) select '0001', 'a' union all select '0001', 'b' union all select '0002' , 'cc' union all select '0002' , 'dd' union all select '0002' , 'jj' union all select '0003' , 'kk'create table #aa(id int identity(1,1),seqno varchar(15),fdi varchar(20),fdis varchar(1000)) declare @seqno varchar(15),@fdi varchar(20),@fdis varchar(1000) insert #aa(seqno,fdi) select seqno,fdi from aa order by seqno update #aa set fdis=@fdis,@fdis=(case when seqno=@seqno then @fdis+','++fdi else fdi end),@seqno=seqno from #aa select seqno,fdi=max(fdis) from #aa group by seqnodrop table #aa
CREATE TABLE 表(seqno nvarchar(5),fdi nvarchar(10)) goINSERT 表 SELECT '0001','a' UNION ALL SELECT '0001','b' UNION ALL SELECT '0002','cc' UNION ALL SELECT '0002','dd' UNION ALL SELECT '0002','jj' UNION ALL SELECT '0003','kk' goCREATE FUNCTION getStr(@_seqno varchar(6)) RETURNS nvarchar(4000) AS BEGIN DECLARE @str nvarchar(4000) SET @str='' SELECT @str=@str+fdi+',' FROM 表 WHERE seqno=@_seqno SET @str=substring(@str,1,len(@str)-1) return (@str) END goSELECT seqno,dbo.getStr(seqno) FROM 表 GROUP BY seqno go 0001 a,b 0002 cc,dd,jj 0003 kk
create table ttt (seqno varchar(4),fdi varchar(100)) insert into ttt select '0001' , 'a' union all select '0001' , 'b' union all select '0002' , 'cc' union all select '0002' , 'dd' union all select '0002' , 'jj' union all select '0003' , 'kk' create function f_combstr(@seqno varchar(4)) returns varchar(100) begin declare @ch varchar(100) set @ch='' select @ch=@ch+fdi+',' from ttt where seqno=@seqno return left(@ch,len(@ch)-1) end select seqno,dbo.f_combstr(seqno) from ttt group by seqno
参考http://blog.csdn.net/ningoo/archive/2005/01/07/244051.aspx
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @Str AS VARCHAR(50)
SET @Str = ''
SELECT @Str = @Str + ',' + RTRIM(fdi) FROM tb WHERE seqno = @seqno
SET @Str = SUBSTRING(@Str, 2, LEN(@Str))
RETURN @Str
END--调用
SELECT seqno, dbo.GetString(seqno) AS fdi
FROM tb
GROUP BY seqno
insert aa(seqno,fdi)
select '0001', 'a'
union all select '0001', 'b'
union all select '0002' , 'cc'
union all select '0002' , 'dd'
union all select '0002' , 'jj'
union all select '0003' , 'kk'create table #aa(id int identity(1,1),seqno varchar(15),fdi varchar(20),fdis varchar(1000))
declare @seqno varchar(15),@fdi varchar(20),@fdis varchar(1000)
insert #aa(seqno,fdi)
select seqno,fdi from aa
order by seqno
update #aa
set fdis=@fdis,@fdis=(case when seqno=@seqno then @fdis+','++fdi else fdi end),@seqno=seqno
from #aa
select seqno,fdi=max(fdis)
from #aa group by seqnodrop table #aa
goINSERT 表 SELECT '0001','a'
UNION ALL SELECT '0001','b'
UNION ALL SELECT '0002','cc'
UNION ALL SELECT '0002','dd'
UNION ALL SELECT '0002','jj'
UNION ALL SELECT '0003','kk'
goCREATE FUNCTION getStr(@_seqno varchar(6))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @str nvarchar(4000)
SET @str=''
SELECT @str=@str+fdi+',' FROM 表 WHERE seqno=@_seqno
SET @str=substring(@str,1,len(@str)-1)
return (@str)
END
goSELECT seqno,dbo.getStr(seqno) FROM 表 GROUP BY seqno
go
0001 a,b
0002 cc,dd,jj
0003 kk
(seqno varchar(4),fdi varchar(100))
insert into ttt
select '0001' , 'a' union all
select '0001' , 'b' union all
select '0002' , 'cc' union all
select '0002' , 'dd' union all
select '0002' , 'jj' union all
select '0003' , 'kk'
create function f_combstr(@seqno varchar(4))
returns varchar(100)
begin
declare @ch varchar(100)
set @ch=''
select @ch=@ch+fdi+',' from ttt where seqno=@seqno
return left(@ch,len(@ch)-1)
end
select seqno,dbo.f_combstr(seqno) from ttt group by seqno