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:
'32,43,53,12,41'
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
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 sql-server-group-concat... 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 .
source to share
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.
source to share
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).
source to share