clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1044301 fiddles created (9408 in the last week).

CREATE TABLE Student (class nvarchar(20), course nvarchar(20),studentname nvarchar(20),score int);
 hidden batch(es)


insert into Student select class = N'甲班',course = N'國文' , studentname = N'學生A' , score = 90 insert into Student select class = N'甲班',course = N'國文' , studentname = N'學生B' , score = 88 insert into Student select class = N'甲班',course = N'國文' , studentname = N'學生C' , score = 88 insert into Student select class = N'甲班',course = N'國文' , studentname = N'學生D' , score = 85 insert into Student select class = N'甲班',course = N'國文' , studentname = N'學生E' , score = 80
5 rows affected
 hidden batch(es)


select * from student where 1=1
class course studentname score
甲班 國文 學生A 90
甲班 國文 學生B 88
甲班 國文 學生C 88
甲班 國文 學生D 85
甲班 國文 學生E 80
 hidden batch(es)


SELECT distinct score,(SELECT cast(studentname AS NVARCHAR ) + ',' from Student where score = stu.score FOR XML PATH('')) as studentnameNew from Student stu where 1=1 order by score desc
score studentnameNew
90 學生A,
88 學生B,學生C,
85 學生D,
80 學生E,
 hidden batch(es)


select top 3 ROW_NUMBER() OVER(ORDER BY score desc) as Number, score, left(studentnameNew,len(studentnameNew)-1) as studentnameComb from ( SELECT distinct score,(SELECT cast(studentname AS NVARCHAR ) + ',' from Student where score = stu.score FOR XML PATH('')) as studentnameNew from Student stu where 1=1 ) as MM
Number score studentnameComb
1 90 學生A
2 88 學生B,學生C
3 85 學生D
 hidden batch(es)