aggregate function on text ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

aggregate function on text ?

Author

Topic

katarina07
Starting Member

31 Posts

Posted - 2006-06-08 : 11:26:44

Hi,

I know it sounds like a crazy question not worth sql :-),

but would it be possible to have some kind of aggregate function on text variable, that would produce this one record:

fields: Client | OrgUnit_Jan | Amount_Jan | OrgUnit_Feb | Amount_Feb
---------------------------------------------------------------------
values: Client1 | A & B | 30 000 | C & D | 80 000

for the input recors:

fields: Client | OrgUnit_Jan | Amount_Jan | OrgUnit_Feb | Amount_Feb
---------------------------------------------------------------------
values: Client1 | A | 10 000 | C | 30 000
values: Client1 | B | 20 000 | D | 50 000

whereby its not important if I get "A & B" or "B & A".

Thanks
Katarina

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-08 : 11:51:16

You could create a function to conatenate the values
create function x
(@id varchar(10), @type int)
returns varchar(100)
as
begin
declare @s varchar(100)
if @type = 1
select @s = coalesce(@s + ' & ',,'') + OrgUnit_Jan from tbl where Client = @id
else id @type = 2
select @s = coalesce(@s + ' & ',,'') + OrgUnit_Feb from tbl where Client = @id

return @s
end
go

then

select Client, OrgUnit_Jan = dbo.x(Client, 1), sum(Amount_Jan), OrgUnit_Feb = dbo.x(Client, 2), sum(Amount_Feb)
from tbl
group by Client

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources