CODEWITHSUNDEEP
sqlaggregate-functionssybasesap-asesybase-ase15
Herman Zun
Herman Zun

Reputation: 491

Sybase ASE 15 Aggregate Function for strings

I'am findind a way to aggregate strings from differents rows into a single row in sybase ASE 15. Like this: 

id | Name                    Result: id | Names
-- - ----                            -- - -----
1  | Matt                            1  | Matt, Rocks
1  | Rocks                           2  | Stylus
2  | Stylus

Something like FOR XML PATH in T-SQL.

Thanks!

Upvotes: 4

Views: 7142

Answers (5)

Gokul Kannan
Gokul Kannan

Reputation: 87

Have you tried using Select String_Agg(<Column>, '<Seperator>') From <Table Name>

Upvotes: 0

SFA
SFA

Reputation: 1

I know the question is a bit old, but I have had the question recently and this would be a possible solution if you assume an upper bound (10 in my example) on the number of names in the list -

    select id,
           max(case n
                 when 0 then Name
               end)
         + max(case n
                 when 1 then ','+ Name
               end)
         + max(case n
                 when 2 then ','+ Name
               end)
         + max(case n
                 when 3 then ','+ Name
               end)
         + max(case n
                 when 4 then ','+ Name
               end)
         + max(case n
                 when 5 then ','+ Name
               end)
         + max(case n
                 when 6 then ','+ Name
               end)
         + max(case n
                 when 7 then ','+ Name
               end)
         + max(case n
                 when 8 then ','+ Name
               end)
         + max(case n
                 when 9 then ','+ Name
               end) as Names
from (SELECT t.id,
             t.Name,
             count(t2.Name) as n
      FROM T t
           left join T t2
             on t2.id = t.id
            and t2.Name < t.name
      -- WHERE 
      group by t.id, t.Name) x
group by id

Upvotes: 0

Prashant Gautam
Prashant Gautam

Reputation: 619

Write below query :- select id, cursorfunc(id) from table

Then create below cursor which is used in above query DECLARE ListCurs CURSOR FOR

//Query to fetch the name select name from table where id=@id

OPEN ListCurs SELECT @Status = 0 WHILE @Status = 0 BEGIN FETCH ListCurs INTO @name

IF @Status = 0 
BEGIN
   SELECT  @res = CASE WHEN @res IS NULL THEN '' ELSE @res + '& ' END + @name
END

END CLOSE ListCurs RETURN (@res)

Upvotes: 2

markp-fuso
markp-fuso

Reputation: 35256

Sybase ASE does not have any string aggregate functions like list() or group_concat(); and while there is some support for FOR XML, it does not include support for the PATH option/feature.

Assuming you could have an unknown/variable number of rows to append, your only (ASE 15) T-SQL option would be a cursor-based solution.

If you find yourself working with ASE 16 you could write a user-defined function (UDF) to accomplish the task, eg: emulate group_concat() in ASE 16

Upvotes: 6

Daniel Vaca
Daniel Vaca

Reputation: 127

You could try this:

select id,list(Names,',' order by id) from TableName a group by id

Upvotes: -3

Related Questions