0

I am using MS SQL Server.

I have a table of students like this:

StudentID,Teacher
123,Adams
124,Adams
125,Johnson
126,Johnson
127,Adams
128,Marks

I need to have output that looks like this:

Teacher,Students
Adams,123|124|127
Johnson,125|126
Marks,128

I appreciate your help. Thank you.

user3513237
  • 995
  • 3
  • 9
  • 26

1 Answers1

2
declare @table_var table(
    student_id INT,
    teacher varchar(50)
)

insert into @table_var
select 123, 'Adams' union all
select 124, 'Adams' union all
select 125, 'Johnson' union all
select 126, 'Johnson' union all
select 127, 'Adams' union all
select 128, 'Marks'

select * from @table_var

select
    teacher,
    students = stuff((select '|' + convert(varchar, t2.student_id)
                    from @table_var t2 
                    where t1.teacher = t2.teacher 
                    for xml path(''))
                ,1,1,'')
from @table_var t1
group by t1.teacher
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • Thank you so much. I got it working. I am not familiar with some of the aspects of this query. Would you mind explaining them...such as what is "stuff", and for xml? Thanks again. – user3513237 Aug 24 '14 at 02:54
  • The string inside path() is the name of the row-level node and the name of the column is the name of the node. In this case, we both have empty string as our node names, as evidenced by for xml path('') and convert(varchar, t2.student_id) . This results to 123124125126.. Then we add | before each student_id which then results to |123|124|125|126... The stuff() is now used to get rid of the leading delimiter |. For reference: http://www.sqlservercentral.com/Forums/Topic1344161-391-1.aspx – Felix Pamittan Aug 26 '14 at 03:40