priyanka

priyanka

  • NA
  • 215
  • 119.8k

Stuff Function In Sql Server

Feb 25 2018 11:34 PM
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.

Answers (2)