CODEWITHSUNDEEP
sqlfunctiondb2db2-luw
Luiz
Luiz

Reputation: 141

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

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

Upvotes: 0

Views: 4822

Answers (1)

Maksim Zinal
Maksim Zinal

Reputation: 86

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.

Upvotes: 2

Related Questions