I think this might work for you:select a.scanned_id ,stuff((select ','+b.scanned_id from thetable as b where b.scanned_id like 'A%' and b.scanned_order between a.scanned_order+1 and a.next_student_order-1 for xml path('') ),1,1,'' ) as answer_code from (select a.scanned_id ,a.scanned_order ,min(isnull(b.scanned_order,99999999)) as next_student_order from thetable as a left outer join thetable as b on b.scanned_order>a.scanned_order and b.scanned_id not like 'A%' where a.scanned_id not like 'A%' ) as a
The above query has not been syntax checked, so please bear over with me, if this is the case.