Q: How to generate SQL output with pipes with multiple tables I am using SQL Server.
I have a table of students like this:
StudentID   TeacherNumber
   123          1 
   124          1
   125          2
   126          2
   127          1
   128          3

I also have a table of teachers like this:
TeacherNumber  TeacherName
    1          Adams
    2          Johnson
    3          Marks

I need to have output that looks like this:
TeacherNumber  Teacher  Students
     1         Adams    123|124|127
     2         Johnson  125|126
     3         Marks    128

I appreciate your help. Thank you.
I posted a similar question previously, and got a response that worked here:
How to generate sql output as piped
Now that I added another table I am having trouble. I appreciate the help.

A: Fiddle:
http://sqlfiddle.com/#!6/27600/29/0
Query:
select distinct st1.teachernumber,
                teachername as teacher,
                stuff(( select '|' + cast(st2.studentid as varchar(20))
                        from students st2
                        where st1.teachernumber = st2.teachernumber
                        order by st2.studentid
                            for xml path('')
                                ),1,1,'') as students
from students st1
join teachers t
  on st1.teachernumber = t.teachernumber

The reason I had to convert STUDENTID to VARCHAR is because by adding the pipe character that data type would no longer be valid and you'd get an error. You have to cast it as varchar to get the pipe delimiter to work with an integer field (I assume STUDENTID is an INT field).
Output:
| TEACHERNUMBER | TEACHER |    STUDENTS |
|---------------|---------|-------------|
|             1 |   Adams | 123|124|127 |
|             2 | Johnson |     125|126 |
|             3 |   Marks |         128 |

