|
katarina07
Starting Member
Slovakia
31 Posts |
 Posted - 06/08/2006 : 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
United Kingdom
12543 Posts |
Posted - 06/08/2006 : 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. |
 |
|
aggregate function on text ?
Forum Locked
