Concatenate students

0

Please login or register to vote for this query.

(click on this box to dismiss)

Cognitive Sciences Meta

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

declare @student table
(
  stu_id int,
  s_name nvarchar(max),
  s_subject nvarchar(max),
  marks int
)

insert into @student
select 123, 'Sam', 'English',     86 union all
select 123, 'Sam', 'Maths',       93 union all
select 123, 'Sam', 'Chemistry',   85 union all
select 124, 'Emma',    'Biology',     80 union all
select 125, 'Abbey',   'Physics',     90 union all
select 125, 'Abbey',   'Computer',    95 union all
select 125, 'Abbey',   'ED',      75

select S1.stu_id,
       S1.s_name,
       stuff((select ','+S2.s_subject
              from @student as S2
              where S2.stu_id = S1.stu_id
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') as s_subject,
       stuff((select ','+cast(S2.marks as varchar(max))
              from @student as S2
              where S2.stu_id = S1.stu_id
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') as marks
from @student as S1
group by S1.stu_id, S1.s_name
having count(*) >= 3

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results