Aggregate Functions on char fields?

hi,
i wondered whether there is any way to simulate a kind of aggegate function
that summerizes char/varchar fiealds.
to make myself clear, please look at the following table t1 which has 2 int
fields:
f1 | f2
--
1 | 10
1 | 20
2 | 30
select f1, sum(f2) s1 from t1 group by f1
the result would be:
f1 | s1
--
1 | 30
2 | 30
so far so good.
now, please look at the following table t2 which as 1 int field and 1 char
field:
f1 | f2
--
1 | A
1 | B
2 | C
select f1, sum(f2) s1 from t2 group by f1
i want the result to be:
f1 | s1
--
1 | A,B
2 | C
is there any way to do it through 1 query only?
thanks!edo
First of all it has nothing to do with aggregates. It is called a
contacenation
Second, I'd strongly recommend you doing such reports on the client side
create table w
(
id int,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"edo" <ewilde@.nana.co.il> wrote in message
news:uHe0RT1lFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi,
> i wondered whether there is any way to simulate a kind of aggegate
> function
> that summerizes char/varchar fiealds.
> to make myself clear, please look at the following table t1 which has 2
> int
> fields:
> f1 | f2
> --
> 1 | 10
> 1 | 20
> 2 | 30
> select f1, sum(f2) s1 from t1 group by f1
> the result would be:
> f1 | s1
> --
> 1 | 30
> 2 | 30
> so far so good.
> now, please look at the following table t2 which as 1 int field and 1 char
> field:
> f1 | f2
> --
> 1 | A
> 1 | B
> 2 | C
> select f1, sum(f2) s1 from t2 group by f1
> i want the result to be:
> f1 | s1
> --
> 1 | A,B
> 2 | C
>
> is there any way to do it through 1 query only?
>
> thanks!
>|||Thanks !!