SQL: concatenated table id into string

I need to export data from a SQL database (some groups use SQL Server, some Oracle, etc.) to CSV, where I need an id from a concatenated table in a row.

For example, I have a class table and a student table. From the classes, I need id, name, section, time and students. I need a string for the ID of the students in the class. Results return columns: id, name, section, time, student_ids. The "student_ids" column must be a delimited string, for example:



The line result will look something like this:

"1405,Computer Science,101,12-1:30,'32,43,53,12,41'"


The number of students in the class does not match, there may be one student or 20 students. I was thinking about using a while loop using SQL Server to get this data, or a temp table that concatenates the keys, but I want to know if there is a way to use standard SQL for this, so that the script can be portable.

Notes: I know the output format is not ideal, but the only option is that we will need to pull a different entry for each class / student combo and then have to concatenate the ID separately.


source to share

3 answers

Different RDBMSs have different ways of doing this kind of query.

If you are using MySQL you should take a look at the function GROUP_CONCAT


In Firebird, you have a function LIST


If you are using SQL Server there are some answers to some questions in SO under ... The usual approach uses the construct FOR XML PATH

. A good example is the following: SQL Query to get the aggregated result in comma delimited together with group by column in SQL Server

There are several ways to do this in Oracle, there is a good article here .



If it's SQL Server, I tend to do it like this:

SELECT c.classID, c.other columns,
STUFF((SELECT ',' + convert(varchar,StudentID) 
    FROM StudentClass sc where c.ClassId = sc.ClassID
    FOR XML PATH('')),1,1,'') as StudentIdList
from ClassTable c


This assumes that you have ClassTable

one that has an ID ClassId

as well as a link table StudentClass

that contains both StudentId

and ClassId


EDIT: You must have some kind of table with student and class information, so they are related to each other. Either the anchor table, or if only one class is allowed per student, then it is possibly ClassId

stored in the students table. Either way, the above will help you solve your problem.



The answer to your question is no, this cannot be done in standard SQL.

This is because you are trying to put multiple values ​​in the same field, which is in violation of first normal form.

You can do this using custom sql, but sql will be different for each database.

However, you can also do this using a tablix in SSRS (which can use the same standard SQL to access tables in both Oracle and SQLServer).



All Articles