Progress 12.2 - Concat rows with same ID

Question

I use the database Progress 12.2 and I want to group rows with same id

For example I have

ID Code
1   PB
1   RO

And I want :

ID Code
1   PB, RO

This is my request :

SELECT id, code FROM table WHERE table.id = 1

I tried String_agg, Group_concat ... but nothing works. Anyone has an idea ?

Regards,

Answer 1

You can use someting like that;

DECLARE @TBL TABLE (ID Int, Code nvarchar(5))
INSERT INTO @TBL values (1 ,'PB'),(1 ,'RO')

SELECT DISTINCT
T1.ID,
CONCAT_STRING = STUFF((
          SELECT ',' + T2.Code
          FROM @TBL T2
          WHERE T2.ID = T1.ID
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM @TBL T1

The result will be like this;

Answer 2

What is the code you tried with GROUP_CONCAT?

Try this query:

SELECT id, GROUP_CONCAT(code SEPARATOR ', ') AS code_list
FROM table
WHERE id = 1
GROUP BY id

If this won't work, another solution is loading the codes into a variable before displaying:

DEFINE VARIABLE c_id          AS INTEGER   NO-UNDO.
DEFINE VARIABLE c_concatenate AS CHARACTER NO-UNDO.

FOR EACH TABLE_name WHERE TABLE.id = 1 NO-LOCK:

    IF c_concatenate <> "" THEN 
        ASSIGN c_concatenate = c_concatenate + "," + TABLE.CODE.
    ELSE ASSIGN c_concatenate = TABLE.CODE.

END.

/* DISPLAY here */