Group by concatenation with commas?

Question

So we have a roster table like this:

 Course, Class, Period, Location, Teacher ID, Student ID
ECC31|Counting and Cardinality (K3)|2(B)|No data|100|1248|15179
ECC31|Counting and Cardinality (K3)|2(B)|No data|100|1248|15244
ECC31|Counting and Cardinality (K3)|2(B)|No data|100|1248|15257
.....

Same Class/Different Students. Is there an SQL approach to have it concatenated with commas? Say one row for each class? Was thinking of Group by Function with a concatenator?

Example of output

ECC31|Counting and Cardinality (K3)|2(B)|No data|100|1248|15179,15244,15257
ECC32|Counting and Cardinality 2 (K3)|3(B)|No data|100|1267|15123,15143,15432

Thanks!

It dont really matter, will use SQL Server or MySQL because Im importing it from a CSV. Thanks for the real quick response. — Enrique Bravo, Jul 10 '17 at 20:27
It matters because the functions are different between different dbms. It's useless if we give solutions for the wrong dbms. — Eric, Jul 10 '17 at 21:11

score 1 · Accepted Answer · answered Jul 10 '17 at 21:01

In MySQL you can use the GROUP_CONCAT() function.

SELECT Course, Class, Period, Location, TeacherID, GROUP_CONCAT(StudentID)
FROM roster
GROUP BY Course, Class, Period, Location, TeacherID

Note that the string of concatenated student ids has a default length limit of 1024 characters. You can increase this using the group_concat_max_len option.

As you can read in the manual, the elements of the list are separated by commas by default, but you can customize this. Refer to the manual for details and examples.

Microsoft SQL Server does not have the GROUP_CONCAT() function. This is why commenters were asking what database you're using. Different implementations of SQL databases have different features and functions.

See past questions like Simulating group_concat MySQL function in Microsoft SQL Server 2005? for solutions in Microsoft SQL Server.

Thank you. Worked like a charm. Didnt know that group existed. — Enrique Bravo, Jul 13 '17 at 15:01

Group by concatenation with commas?

1 Answers1