sql 的一个小问题！！！！求助

[qojnbbqtg]

表a
列 a1 a2
记录 1 a
1 b
2 x
2 y
2 z
用select查询得：
1 ab
2 xyz

[wonture]

create function f_test(@col as varchar(50))
returns varchar(max)
as
begin
declare @s varchar(max)
set @s=''
select @s=@s+a2 from a where a1=@col
return(left(@s,len(@s)-1))
end
go

select a1,dbo.f_test(a1) from a group by a1
drop function f_test

[xzwyl]

重复帖子要求，我也是刚刚看到，详见：
http://topic.csdn.net/u/20081030/14/a24f5251-155d-46b7-b03d-96df781fb47c.html

[pt1314917]

--合并就可以了。
create function g_fs(@a1 int)
returns varchar(100)
as
begin
    declare @sql varchar(100)
    select @sql=isnull(@sql+'','')+ a2 from a where a1 =@a1 
    return @sql
end
go

select distinct a1,a2=dbo.g_fs(a1) from a

[lihuanmei]

CREATE TABLE tb(col1 int,col2 varchar(10))
INSERT tb SELECT 1,'a'
UNION ALL SELECT 1,'b'
UNION ALL SELECT 2,'x'
UNION ALL SELECT 2,'y'
UNION ALL SELECT 2,'z'

--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))

--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old int,@col1 int,@col2 varchar(10),@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+@col2
ELSE
BEGIN
INSERT @t VALUES(@col1_old,@s)
SELECT @s=@col2,@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,@s)
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t

DROP TABLE tb
用它试一下，我刚才测试通过的，若已经有表了，就把前面测试创建的表及数据替代就可以了

[lemontreelau]

DECLARE @TA TABLE(a1 INT)
INSERT @TA
SELECT 1  UNION ALL 
SELECT 2


DECLARE @TB TABLE(a1 INT ,a2 VARCHAR(2))
INSERT @TB
SELECT 1,  'a' UNION ALL 
SELECT 1,  'b' UNION ALL 
SELECT 2,  'x' UNION ALL 
SELECT 2,  'y' UNION ALL 
SELECT 2,  'z'



SELECT A.a1,B.a2 as C INTO TB 
FROM @TA AS A JOIN @TB AS B ON A.a1=B.a1 
GO
CREATE FUNCTION GetString(@a int)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @SQL VARCHAR(100)
    SET @SQL=''
    SELECT @SQL=@SQL+C FROM TB WHERE a1=@a 
                  RETURN @SQL
END
GO
SELECT a1,a2=dbo.GetString(a1) FROM  TB GROUP BY a1