Best way to concat 1 to n values into single field from two tables

Question

T-SQL

Imagine two tables looking like this:

Table: students
==============================
| TeacherID  | SName         |
| 1          | Thompson      |
| 1          | Nickles       |
| 2          | Cree          |
==============================

Table: teacher
====================================================
| TeacherID  | TName         | + many other fields |
| 1          | Pipers        |                     |
| 2          | Slinger       |                     |
====================================================

The field names are completely arbitrary.

I want to create a query with the following output:

================================================================
| TeacherName | many other fields | Students                   |
| Pipers      |                   | Thompson,Nickles           |
================================================================

Currently I have something like this:

SELECT *
FROM teacher
LEFT JOIN (
    SELECT DISTINCT 
        EL2.teacherID, 
        STUFF(( SELECT ',' + SName
        FROM students
        WHERE EL2.teacherID = students.teacherID
        FOR XML PATH('')
        ),1,1,'') AS "Students"
    FROM students, teacher EL2) t1
    ON t1.teacherID = teacher.teacherID
WHERE t1.Students LIKE '%Thompson%'

This works and gives me what I need. The WHERE clause is to illustrate that I also absolutely need to be able to filter if a teacher has that student, but then put all students that teacher has into the concated field.

My question now is if there is a better way to do this. I already looked at this: Concatenate many rows into a single text string?

But it didn't help me much because one I couldn't get it to work with two seperate tables and two I couldn't filter the way I needed.

The SQL Management Studio execution plan indicates that the SELECT DISTINCT is very expensive and others have said that the reliance on XML PATH is not optimal because it's behaviour can change.

Answer 1

Be carefull with a DISTINCT on names, as you might have two students with the same name! And btw: GROUP BY is in most cases a better performing approach to get a distinct list...

You might try something like this:

SELECT t.*
      ,STUFF(( SELECT ',' + s.SName
        FROM students AS s
        WHERE t.teacherID = s.teacherID
        FOR XML PATH('')
        ),1,1,'') AS Students
FROM teacher AS t
WHERE EXISTS(SELECT 1 FROM students AS x WHERE x.teacherID=t.teacherID /*AND [PUT YOUR FILTER HERE]*/)

If I understand this correctly you want to find only teachers where one given student is connected to the teacher. And in this case you want to find all students bound to all teachers connected to the given student, correct?

At the end you find a /*AND [PUT YOUR FILTER HERE]*/ At this place you should put something like AND x.StudentId=123. This will filter the teachers to the rows connected with this student only. For these teachers all students are concatenated...