0

For simplicity, we have the following tables

  1. Teachers
  2. Students

Let's assume that each teacher has many students (and not the other way around, let's assume that each student has only one teacher).

How do I display the data such that: 

Col 1 - Teachers - Students
Row 1 - Teacher A - Student A, Student B, Student C
Row 2 - Teacher B - Student X, Student Y, Student Z
Saturn K
  • 2,705
  • 4
  • 26
  • 38
  • 2
    for concating names `for xml` approach is recommended, search for it - this question is one of very common – Ivan Starostin Feb 24 '16 at 19:57
  • Another option - OPENROWSET? – Leptonator Feb 24 '16 at 19:58
  • @IvanStarostin, seconded. If you want just two columns, with students being a CSV, FOR XML PATH('') with STUFF is the best and fastest approach. – ps2goat Feb 24 '16 at 19:59
  • There are several ways of achieving what you want. The following article enumerates all the different techniques possible: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ – qux Feb 24 '16 at 19:59
  • http://stackoverflow.com/questions/21623593/what-is-the-meaning-of-select-for-xml-path-1-1 – ps2goat Feb 24 '16 at 20:00
  • Are you 100% sure that this formatting needs to be done by the database and not in your application code? – PM 77-1 Feb 24 '16 at 20:09
  • Ideally it would be done in the code... however this SPROC is being used in 10 places and expects to keep the same number of rows (i.e. if I joined to a many table it would increase the number of rows and duplicate fields) – Saturn K Feb 24 '16 at 22:26

0 Answers0