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.

Microsoft SQL Server 2008

Last Comment

r_pat72

8/22/2022 - Mon

Chris Luttrell

1/4/2013

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

r_pat72

1/4/2013

ASKER

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

ASKER CERTIFIED SOLUTION

Chris Luttrell

1/4/2013

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

r_pat72