Andrew
based on your DDL I think we don;t really need to use a cursor inside udf.
create table trans(
nafe int,
transport char(3),
entregas int,
cidade varchar(20),
peso decimal(9,2)
)
go
insert into trans
values(100, 'ttt', 12, 'Mariana', .5)
insert into trans
values(100, 'ttt', 12, 'Patos', .5)
insert into trans
values(100, 'ttt', 12, 'Corinto', .5)
create function dbo.fn_my ( @id int)
returns varchar(100)
as
begin
declare @w varchar(100)
set @w=''
select @w=@w+ cidade+',' from trans where nafe=@id
return @w
end
select trans.nafe,trans.transport,trans.entregas,
dbo.fn_my (dd.nafe),trans.peso
from
(
select distinct nafe from trans
)
as dd join trans on dd.nafe=trans.nafe
group by trans.nafe,dd.nafe,trans.transport,trans.entregas,trans.peso
[quoted text, click to view] "Pozolotin Andrew" <pozolotin@sibproject.com> wrote in message
news:ewjp6LdnDHA.2652@TK2MSFTNGP09.phx.gbl...
> try this
>
> create table trans(
> nafe int,
> transport char(3),
> entregas int,
> cidade varchar(20),
> peso decimal(9,2)
> )
> go
>
> insert into trans
> values(100, 'ttt', 12, 'Mariana', .5)
> insert into trans
> values(100, 'ttt', 12, 'Patos', .5)
> insert into trans
> values(100, 'ttt', 12, 'Corinto', .5)
> go
>
> create function fn_cidade (
> @nafe int,
> @transport char(3),
> @entregas int
> )
> returns varchar(255)
> as
> begin
> declare @v varchar(20)
> declare @ret varchar(255)
> declare c1 cursor for
> (select cidade from trans
> where (nafe = @nafe) and (transport = @transport) and (entregas =
@entregas)
> )
> select @ret = ''
> open c1
> fetch next from c1 into @v
> while (@@fetch_status <> -1)
> begin
> select @ret = @ret + @v + ','
> fetch next from c1 into @v
> end
> close c1
> deallocate c1
> select @ret = left(@ret, len(@ret)-1)
> return @ret
> end
> go
>
> --query
> select nafe, transport, entregas, dbo.fn_cidade(nafe, transport,
entregas),
> sum(peso) from trans
> group by nafe, transport, entregas
> go
>
> drop function fn_cidade
> drop table trans
>
> Andrew
>
> "Frank Dulk" <fdulk@bol.com.br> wrote in message
> news:uWIZu1bnDHA.2512@TK2MSFTNGP09.phx.gbl...
> > I have a query that return the following data:
> >
> > nafe Transportadora nºentregas cidade peso(kg)
> > 100 tttt 12 Mariana 500
> > 100 tttt 12 Patos 500
> > 100 tttt 12 Corinto 500
> > etc...
> >
> > I need to do that the result appears:
> > nafe Transportadora nºentregas cidade peso(kg)
> > 100 tttt 12 Mariana,Patos,Corinto 1.500
> >
> > Somebody can help.
> >
> > I thank at once.
> >
> >
>
>
