Stuff Function In Sql Server

My Table Structure is like this

ClassId ClassNm

1 1

2 2

3 3

SubjectId subjectNm

001 Hindi

002 ENGLISH

003 Math

ClassId SubjectId

1 001

1 002

1 003

2 001

2 002

3 003

I want the output in following format

classId Subject

1 001,002,003

My query is something like this,

Select cs.ClassId,cl.ClassNm,

STUFF((Select ','+ sc.Subject from dbo.Class_Subject cs,dbo.Class cl,dbo.Subject sc

where cl.ClassId=cs.ClassId and sc.SubCode=cs.SubCode FOR XML PATH('')) ,1,1,' ')

as Subject

from dbo.Class_Subject cs,dbo.Class cl,dbo.Subject sc where cl.ClassId=cs.ClassId and

sc.SubCode=cs.SubCode Group by cs.ClassId,cl.ClassNm

But I am not getting the proper result.

Plz suggest,what I am doing wrong.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

priyanka8y ago
I got my result.
Thank a lot
+1
Nitin Sontakke8y ago
Have a look at this:

http://www.c-sharpcorner.com/blogs/how-to-get-comma-separated-values-in-sql-server-with-stuff-and-cross-apply-keywords1
+1