sql server Query question

Hi,

I have a student table & has three coulumns. The sample data exists in this table as below.
I want a query which should output(please see in expected output). I don't want to use cursor.

Student table with sample row

StudenRowid  Name   course
1                       paul      Math
1                       paul       English
2                       David     science
2                       David     English
3                       Tim        Matk

Expected output:

Name    course
Paul          Math,English
David         Science,English
Tim          Math


Thanks for your help.
r_pat72Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
Ah, the embedded space, then this is another way to do it and should work.  I just liked the elegance of the REPLACE instead of SUBSTRING
select DISTINCT Name
, substring((select ','+Course from students s where s.SudentRowId = x.SudentRowId for xml path('')),2,2000) as Couse_list
from students x

Open in new window

Just make sure you have the last value for the length in SUBSTRING long enough.

Sample output: 
David	Science,English
paul	Math,English
Tim	Social Science

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
Try this:
select DISTINCT Name
, REPLACE((select Course AS [data()] from #students s where s.SudentRowId = x.SudentRowId for xml path('')), ' ', ',') AS Course_List
from students x

Open in new window

0
 
r_pat72Author Commented:
Hi ,

Thanks for your reply. But if the course is social science, the results which returns is social,science.

StudenRowid  Name   course
1                       paul      Math
1                       paul       English
2                       David     science
2                       David     English
3                       Tim        Math
4                        Tom     Social science

For Tom it returns social,science which should return social science.

Thanks
0
 
r_pat72Author Commented:
thank a lot. This worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.