3

I need to export data from a SQL database (some groups use SQL Server, some Oracle, etc.) into a CSV where I need the id's from the joined table in a string.

For example, I have a table of Classes and a table of Students. From Classes, I need ID, Name, Section, Time, and from Students I need a string of the ID's of Students in the class. The results would return the columns: id,name,section,time,student_ids. The 'student_ids' column should be a delimited string, such as:

'32,43,53,12,41'

The row output would end up being something like:

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

The number of Students per Class is not consistent, there could be one Student, or 20 Students. I thought about using a while loop using SQL Server to obtain this data, or a temp table that aggregates the keys, but I want to know if there is any way using standard SQL to accomplish this, so that the script can be portable.

Notes: I know the output format is not ideal, but the only other option is that we will have to pull a different record for each Class/Student combo, and then will have to aggregate the id's separately.

Sam Johnson
  • 943
  • 1
  • 13
  • 19
  • What variety of SQL? MySQL has GROUP_CONCAT() for example. Also, how will you be using this once it's been generated? We may be able to give alternative processes. – MatBailie Mar 16 '12 at 15:07
  • @Dems Unfortunately the SQL variety varies, almost always either Oracle or a version of SQL Server though – Sam Johnson Mar 16 '12 at 15:14
  • @Dems Also, using the export as a way to receive the data in a standardized format, unfortunately using an additional export like "class_student" is not an option – Sam Johnson Mar 16 '12 at 15:17

3 Answers3

2

Different RDBMS have different ways to perform this kind of query.

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

In Firebird, you have the LIST aggregate function.

If you are using SQL Server, there are some answered questions in SO under the . The usual approach is using the FOR XML PATH construct. A good example is this: SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

There are a few ways of doing that in Oracle, there's a good article here.

Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
1

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 a ClassTable which has an ID of ClassId and also a StudentClass linking table which contains both StudentId and ClassId.

EDIT: You must have some kind of table with both student and class information so the two are linked together. Either a linking table or if each student is only allowed one class, then maybe the ClassId is stored in the student table. Either way, the above should help you to solve your problem.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Tom Chantler
  • 14,753
  • 4
  • 48
  • 53
0

The answer to your question is: no, it can't be done in standard SQL.

This is because you are trying to put multiple values into a single field, which is a violation of first normal form.

You could do it using non-standard sql, but the sql would be different for each database.

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