2

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!

Herman Zun
  • 471
  • 1
  • 6
  • 21

3 Answers3

5

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

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
0

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)

Prashant Gautam
  • 589
  • 8
  • 10
-3

You could try this:

select id,list(Names,',' order by id) from TableName a group by id
Daniel Vaca
  • 159
  • 2
  • 3
  • 15