0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

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

fork query

created mar 1 12 Mikael Eriksson