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

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

CodePudding user response：

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

CodePudding user response：

The answer depends on the DB you use. Lemon showed already good ways for the different DB's. I just want to add another one since STRING_AGG was introduced in SQL Server 2017. If you use an older SQL Server DB, you can use STUFF instead:

SELECT DISTINCT t1.id,
STUFF(
     (SELECT ','   col1
      FROM yourtable t2
      WHERE t1.id = t2.id
      FOR XML PATH (''))
      ,1,1,'')  AS "values"
FROM yourtable t1;

CodePudding user response：

For Oracle 11gR2 and later...

Select
    ID, 
    ListAgg(COL1, ', ') OVER(PARTITION BY ID ORDER BY ID) "VALUES" 
From tbl
Group by ID
Order by ID