How can I concatenate character values in a GROUP BY query in DB2?

Question

Does someone knows how to get that result bellow ?

There is a sceneario

ID          DATE        Description
00041454707 27/07/17    Hospital Sugisawa Pronto Atendimento
00041454707 27/07/17    Hospital Pequeno Príncipe
00041454707 28/07/17    Hospital Sugisawa Pronto Atendimento

Result expected :

ID          DATE        Description
00041454707 27/07/17    Hospital Sugisawa Pronto Atendimento | Hospital Pequeno Príncipe
00041454707 28/07/17    Hospital Sugisawa Pronto Atendimento

Look at result ,I've grouped the first and second one , which has same date 27/07/17 , and concatened in Description field .

I dunno have much background in DB2 .

DB2 V11.1

Possible duplicate of [DB2 comma separated output](https://stackoverflow.com/questions/7188542/db2-comma-separated-output) — mustaccio, Jan 08 '18 at 16:55
LISTAGG is the function - if you check the linked SO question. — data_henrik, Jan 08 '18 at 18:14
Yeah , LISTAGG i can get close to result above , BUT , one thing that you forgot is i've grouped 2 rows in one ! — Luiz, Jan 08 '18 at 18:22

Maksim Zinal · Answer 1 · 2019-11-11T16:02:16.190

1

SELECT id, dateval,
  LISTAGG(descval, ' | ') AS descvals
FROM yourtable
GROUP BY id, dateval

As already mentioned by others here, the trick is done by LISTAGG function. GROUP BY operator collects the rows into a group, and LISTAGG collects the values into a string with the separator specified.

edited Nov 11 '19 at 16:02

answered Nov 11 '19 at 15:29

Maksim Zinal

66
3

A little explanation really improve your answer. – Selim Yildiz Nov 11 '19 at 15:47

How can I concatenate character values in a GROUP BY query in DB2?

1 Answers1