0

I would like to group by the column "ID" but concatenate the column "Country" separated by a comma. Is that even possible?

Main table sample:

ID Country
01 US
02 BR
02 CR

Expectation:

ID Country
01 US
02 BR,CR
Nishant Gupta
  • 3,533
  • 1
  • 11
  • 18
Tana
  • 297
  • 3
  • 13
  • 2
    Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL Server](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Jonas Metzler Jun 01 '22 at 06:25
  • Duplicate and no RDMS specification. – Umut TEKİN Jun 01 '22 at 07:48

1 Answers1

2

According to the DBMS you're using, there are different functions that can be employed to solve your problem. In general it belongs to the class of the aggregation functions, and as such, it requires a column you want to aggregate on (in your case Country) and optionally a column you want to group on (in your case ID).

Given MySQL, SQLite and MariaDB official documentation, you can use the GROUP_CONCAT aggregation function:

SELECT ID,
       GROUP_CONCAT(Country)
FROM tab 
GROUP BY ID

Try MySQL, SQLite or MariaDB demo at the corresponding links.

Given Postgres and SQLServer official documentation, you can use the STRING_AGG aggregation function:

SELECT ID,
       STRING_AGG(Country, ',')
FROM tab 
GROUP BY ID

Try PostgreSQL or SQLServer demo at the corresponding links.

Given OracleDB official documentation, you can use the LISTAGG aggregation function:

SELECT ID,
       LISTAGG(Country, ',') WITHIN GROUP (ORDER BY Country) Country
FROM tab 
GROUP BY ID

Try OracleDB demo at the corresponding link.

Hopefully this is comprehensive enough to help future people to aggregate on strings with the most popular databases.

lemon
  • 14,875
  • 6
  • 18
  • 38