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
--合并就可以了。 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 goselect distinct a1,a2=dbo.g_fs(a1) from a
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 @tDROP TABLE tb 用它试一下,我刚才测试通过的,若已经有表了,就把前面测试创建的表及数据替代就可以了
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
http://topic.csdn.net/u/20081030/14/a24f5251-155d-46b7-b03d-96df781fb47c.html
--合并就可以了。
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
goselect distinct a1,a2=dbo.g_fs(a1) from a
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 @tDROP TABLE tb
用它试一下,我刚才测试通过的,若已经有表了,就把前面测试创建的表及数据替代就可以了
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