MS SQL Server - Aggregate Functions on char fields??

Author

Aggregate Functions on char fields??

edo

2005-08-02, 7:23 am

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!

Uri Dimant

2005-08-02, 7:23 am

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!
>
>

edo

2005-08-07, 3:23 am

Thanks !!

Sponsored Links