title: How to generate SQL output with pipes with multiple tables
body: <p>I am using SQL Server.</p>
  
  <p>I have a table of students like this:</p>
  
  <pre><code>StudentID   TeacherNumber
     123          1 
     124          1
     125          2
     126          2
     127          1
     128          3
  </code></pre>
  
  <p>I also have a table of teachers like this:</p>
  
  <pre><code>TeacherNumber  TeacherName
      1          Adams
      2          Johnson
      3          Marks
  </code></pre>
  
  <p>I need to have output that looks like this:</p>
  
  <pre><code>TeacherNumber  Teacher  Students
       1         Adams    123|124|127
       2         Johnson  125|126
       3         Marks    128
  </code></pre>
  
  <p>I appreciate your help. Thank you.</p>
  
  <p>I posted a similar question previously, and got a response that worked here:</p>
  
  <p><a href="https://stackoverflow.com/questions/25356459/how-to-generate-sql-output-as-piped">How to generate sql output as piped</a></p>
  
  <p>Now that I added another table I am having trouble. I appreciate the help.</p>