create function tmpAdd (@t1 varchar(20))
return varchar(200)
as
declare val varchar(200)select val=val+t2 from table1 where t1=@t1;return val;goselect t1,dbo.tmpAdd(t2) as t2 from table1 group by t1
return varchar(200)
as
declare val varchar(200)select val=val+t2 from table1 where t1=@t1;return val;goselect t1,dbo.tmpAdd(t2) as t2 from table1 group by t1
[create] table table1
(t1 varchar(2),t2 varchar(10))insert table1 values ('a','AAA')
insert table1 values ('b','BBB')
insert table1 values ('a','KKK')
gocreate function f_he(@col varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+'+'+t2 from table1 where t1=@col
return(stuff(@sql,1,1,''))
end
goselect t1,dbo.f_he(t1) as t2 from table1 group by t1drop function f_he
drop table table1/*-----------结果-----------*/t1 t2
-------- ------------------------------------
a AAA+KKK
b BBB(所影响的行数为 2 行)
INSERT tb SELECT 'a','AAA'
UNION ALL SELECT 'b','BBB'
UNION ALL SELECT 'a','CCC'
UNION ALL SELECT 'b','DDD'GO
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+'+'+col2
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
--结果
--COL1 COL2
--a AAA+CCC
--b BBB+DDD
GO
t1 t2
a AAA
b BBB
a KKK
.........
我想查询得出如下结果:
t1 t2
a AAA + KKK
b BBB
...........楼主试试这个简单的句子,我觉得好像就可以耶:
select t1 as t1, sum(t2) as t2 from table1 group by t1
不说的方法不行吧,你想下如果AAA=111。BBB=222
如果你用SUM的话,那得到就是333,而不是111+222
你说呢??
数值的直接用SUM吧