求一个SQL语句

[newmcz]

有一个表
COL1 COL2
5770 35
5770 36
5770 37
5770 40
5770 41

求一个SQL语句得出
COL1 COL2
5770 35,36,37,40,41

谢谢!

[aierong]

1

[aierong]

DECLARE @aa TABLE(col1 INT,col2 int)
INSERT @aa VALUES(5770,35)
INSERT @aa VALUES(5770,36)
INSERT @aa VALUES(5770,37)
INSERT @aa VALUES(5770,40)
INSERT @aa VALUES(5770,41)

declare @cl int
declare @sql varchar(100)
select @sql=''
declare cur cursor for select col2 from @aa
open cur
FETCH FROM cur INTO @cl
WHILE @@FETCH_STATUS = 0
begin
select @sql=@sql+','+cast(@cl as varchar(100))
FETCH FROM cur INTO @cl
end
select @sql=right(@sql,len(@sql)-1)
print @sql

select distinct col1,col2=@sql from @aa

[yujohny]

CREATE FUNCTION getallstr(@aa NVARCHAR(4))
RETURNS NVARCHAR(200)
AS
先建一个函数
BEGIN
DECLARE @str NVARCHAR(200)
SET @str =''

SELECT @str =@str+COL2+',' FROM 表 WHERE COL1 =@aa

SET @str = LEFT(@str,LEN(@str)-1)

RETURN @str
END

然后调用 select col1,dbo.getallstr(col1) FROM 表 Where col1 ='5770'

[yujohny]

先建一个函数

CREATE FUNCTION getallstr(@aa NVARCHAR(4))
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @str NVARCHAR(200)
SET @str =''

SELECT @str =@str+COL2+',' FROM 表 WHERE COL1 =@aa

SET @str = LEFT(@str,LEN(@str)-1)

RETURN @str
END

然后调用 select col1,dbo.getallstr(col1) FROM 表 Where col1 ='5770'

[newmcz]

up

[txlicenhe]

--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(col2 as varchar) from 表A where col1=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select distinct col1,dbo.fmerg(col1) from 表A