Skip to content
Advertisement

Concate values of a certain column in a different column depending on id in SQL

id col1
1 John
1 Mary
2 Patricia
2 Charlie
3 Jane
3 Karen
3 MJ
3 Roland

The output should look like this

id values
1 John,Mary
2 Patricia, Charlie
3 Jane, Karen, MJ,Roland

Advertisement

Answer

In MySQL, MariaDB and SQLite, you can use the GROUP_CONCAT aggregation function:

SELECT id,
       GROUP_CONCAT(col1) AS values_
FROM tab
GROUP BY id

In PostgreSQL and SQL Server, you can use the STRING_AGG aggregation function:

SELECT id,
       STRING_AGG(col1, ',') AS values_
FROM tab
GROUP BY id

In Oracle and DB2, you can use the LISTAGG aggregation function:

SELECT id,
       LISTAGG(col1, ',') WITHIN GROUP(ORDER BY col1) AS values_
FROM tab
GROUP BY id

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement