1

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.

Community
  • 1
  • 1
user3513237
  • 995
  • 3
  • 9
  • 26
  • http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – realnumber3012 Aug 25 '14 at 03:24
  • What have you tried? please edit your question and put any code that you have tried into the question (even if it isn't working). – Taryn East Aug 25 '14 at 03:30

1 Answers1

2

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 |
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
  • Perfect! Thank you so much. Is there a way to get rid of the trailing pipe? So instead of 123|124|127| it would be 123|124|127 – user3513237 Aug 25 '14 at 04:12
  • @user3513237 yes I just updated the answer to do so, you can change the ",1,1,'') as students" to chop 1 leading 1 trailing. Before I didn't chop the last trailing character. – Brian DeMilia Aug 25 '14 at 04:13
  • I am having issues running this on my SQL Server 2008 box. Query has been running for 3 minutes now with no results yet... – user3513237 Aug 25 '14 at 04:55
  • @user3513237 how big is the table? Is teachernumber indexed and/or a PK? – Brian DeMilia Aug 25 '14 at 10:35