--未测试,信手写的 create table t1(cName varchar(10)) go insert into t1 values('Li') insert into t1 values('Wang') insert into t1 values('Zhao') godeclare @s varchar(500) declare @i int declare @name varchar(10) set @i=0 set @s='' declare my_cursor cursor for select * from t1 open my_cursor fetch next from my_cursor into @name while(@@fetch_status=0) begin if(@i=0) begin set @s=@s+@name end else begin set @s=@s+','+@name end set @i=@i+1 fetch next from my_cursor into @name end close my_cursor print @s drop table t1
参考下面的帖子:
http://topic.csdn.net/u/20080917/10/CE5BFF23-D25C-4E41-BB28-B1FCFEEA3E50.html
SELECT name=DBO.SQL_Aggregate(name)
FROM tb1
GROUP BY name
A B
----
Q W
Q M
Q S
即A列对应多个B列,现在要返回 A列值为Q的B列为一条记录中间用空格隔开.即W M S下面函数可以搞定
CREATE FUNCTION dbo.getBbyA
(
@C varchar (50)
)
RETURNS varchar (100)
AS
BEGIN
DECLARE @CODE VARCHAR(100)
set @CODE=''
SELECT @CODE=@CODE+B+' ' from table where A=@C
RETURN @CODEEND
--未测试,信手写的
create table t1(cName varchar(10))
go
insert into t1 values('Li')
insert into t1 values('Wang')
insert into t1 values('Zhao')
godeclare @s varchar(500)
declare @i int
declare @name varchar(10)
set @i=0
set @s=''
declare my_cursor cursor for
select * from t1
open my_cursor
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
if(@i=0)
begin
set @s=@s+@name
end
else
begin
set @s=@s+','+@name
end
set @i=@i+1
fetch next from my_cursor into @name
end
close my_cursor
print @s
drop table t1
其实就是循环取出来的cusor,然后把里面的字符串连起来。
DECLARE @STR NVARCHAR(4000)
SET @STR =''
SELECT @STR = @STR +','+[name]
FROM [tb1]
SET @STR = STUFF(@STR ,1,1,'')
PRINT @STR
END
declare @namestr as varchar(8000);
set @namestr=''
select @namestr=@namestr+name+',' from tb1