Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    3

    Unanswered: [Resolved] Create aggregate function in sybase

    Hi all,

    How can i create an aggregate function.

    My table is :
    Code:
    ID       name       locality
    ------ --------- ----------
    1         foo           FRANCE
    2         foo           US
    3         bar           SPAIN
    4         bar           UK
    and i want the result :
    Code:
    name      localities
    -------- ------------
    foo          FRANCE, US
    bar          SPAIN, UK
    i would like to write a concat() aggregate function
    to use in my select like :

    select name, concat(locality) "localities" from personnal group by name

    Thanks a lot if you can help me (and if you can't too )

    By
    Last edited by aziz93or; 09-22-05 at 11:15. Reason: Resolved

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Can’t help with a function, but you can do this query
    Code:
    select * from #t1
    select a.name, min(a.locality) + "," + min(b.locality) as localities
    from #t1 a, #t1 b
    where a.name=b.name
      and a.id<b.id
    group by a.name
    
     id          name       locality   
     ----------- ---------- ---------- 
               1 foo        france     
               2 foo        us         
               3 bar        spain      
               4 bar        uk         
    
    (4 rows affected)
     name       localities            
     ---------- --------------------- 
     bar        spain,uk              
     foo        france,us             
    
    (2 rows affected)

  3. #3
    Join Date
    Sep 2005
    Posts
    3
    Hi,

    Thanks for your answer.
    I miss to specify one thing for my querry.
    The association between name and locality is 1..N
    So, we can have more than 2 localities for one name.

    your query works for 2 localities but is not generic for N localities.
    I realy need an aggregate function (like min, avg, etc.) that concat varchar.

    Thanks
    Last edited by aziz93or; 09-21-05 at 10:08.

  4. #4
    Join Date
    Sep 2005
    Posts
    3

    [Resolved]

    Hi

    I resolved my problem by the use of cursor !
    I think it is the only one way to aggregate data.

    The method is :

    Code:
    /* temp table for getting the result */
    create table #result (name varchar(20), localities varchar(255))
    
    select @name_prev = null
    
    declare my_crsr cursor for
    select name, locality from my_table
    
    open my_cursor
    
    fetch my_crusor into @name, @loc
    
    while (@@sqlstatus = 0 )
    begin
       if (@name_prev <> @name)
       begin
            /* insert the row in the result set */
            if (@name_prev is not null)
                 insert into #result (name,localities)
                 values (@name_prev, @localities)
    
            select @name_prev = @name,
    
       end
    
       /* concat localities */
       select @localities = @localities + @loc + ', '
    
       fetch my_crusor into @name, @loc
    
    end
    
    close my_crsr
    
    if (@name_prev is not null)
                 insert into #result (name,localities)
                 values (@name, @localities)
    
    
    
    select * from #result
    
    deallocate cursor my_crsr
    drop table #result
    You have to declare used variables, and format the localities, and it would work.

    By
    Last edited by aziz93or; 09-26-05 at 13:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •