Concatenate rows in select query (in Advantage Dat

Question

How can I concatenate rows in select query? (in Advantage Data Architect)

I tried run the following scripts:

The first script:

declare @str string;
set @str = '';
select @str = @str + field_1 from my_table1

But I get a result where all rows contain "false", like this picture:

Second script:

declare @str string;
select @str = coalesce(@str + ', ','') + field_1 from my_table1

This time, all rows are empty (note: the field from "my_table1" is not null).

Picture:

I tried to search the solution on the Internet for Advantage Data Architect, but I could not find a solution.

Answer 1

I'm assuming you want something like GROUP_CONCAT in MySQL or string_agg in Oracle / Postgres.

The general algorithm for that is something like:

DECLARE @S STRING;

DECLARE CURSOR C AS 
SELECT
  CONVERT(field_1, SQL_CHAR) AS "val"
FROM 
  my_table1;

OPEN C;

WHILE FETCH C do
  -- Since @S is NULL for the first row this will ensure 
  -- that the result does not start with the separator.
  @S = COALESCE(@S + ', ' + C.val, C.val);
END;

CLOSE C;

SELECT @S;

A general function can be found on the ADS forum.

PS: This is the reverse of splitting a string into separate rows.