SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 aggregate function on text ?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

katarina07
Starting Member

Slovakia
31 Posts

Posted - 06/08/2006 :  11:26:44  Show Profile
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

United Kingdom
12543 Posts

Posted - 06/08/2006 :  11:51:16  Show Profile  Visit nr's Homepage
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000