Group by and string concatenation in Oracle and MySQL

It's a common operation in DB to have a need of grouping and concatenating several columns in the output result of the query. For example if you want to get make an report for people based on their country from a table containing both the country and the person:

Example 1

On the left is the table and on the right is the concatenated output:

id

Name

Value

id

concat

1

A

3

1

A:3,B:2

1

B

2

2

C:6, R2

2

C

6

3

F:7

2

R

2

4

D:5

3

F

7

4

D

5

Example 2

On the left if the table that will be queried and on the right is the ouput result.

id

Country

Person

country

GROUP_CONCAT(person)

1

Austria

Sue

Ausria

Sue, Anie

2

Austria

Anie

Australia

John, Brian

3

Australia

John

UK

Jim, Tim

4

Australia

Brian

USA

David, Mike, Tom

5

UK

Jim

N. Korea

Joe, Hue, Rick, Jamy

6

UK

Tim

Finland

Kimi

7

USA

David

8

USA

Mike

9

USA

Tom

10

N. Korea

Joe

11

N. Korea

Hue

12

N. Korea

Rick

13

N. Korea

Jamy

14

Finland

Kimi

Oracle group concatenate

This is the oracle version of group concatenation by using the LISTAGG function. As you can see from the code below two things has to be given : the grouping column and the concatenating one.

SELECT country,
  LISTAGG(person, ', ') WITHIN GROUP (
ORDER BY person) "names"
FROM mytable
GROUP BY country;

MySQL concat and group

The mysql version is much simpler by using function GROUP_CONCAT and the example is self-explantory.

SELECT country, GROUP_CONCAT(person) FROM mytable GROUP BY country

country

GROUP_CONCAT(person)

Ausria

Sue, Anie

Australia

John, Brian

UK

Jim, Tim

USA

David, Mike, Tom

N. Korea

Joe, Hue, Rick, Jamy

Related Article