SQLTeam.com | Weblogs | Forums

Dual records to single record


#1

Hi I have a simple query:

Select StudentName, StudentId, Class, Gender, Age
From StudentTbl

and here is the result set:

Student Name Student ID Class Gender Age
Tony T445STD A M 22
Tony T445STD G M 22
Bryan T200STD B M 24
Jessica T323STD C F 21

How do make the result set to:

Student Name Student ID Class Gender Age
Tony T445STD A,G M 22
Bryan T200STD B M 24
Jessica T323STD C F 21

Thank you all


#2
SELECT DISTINCT 
       s.StudentName, s.StudentId, Class = STUFF(c.Class, 1, 1, ''), s.Gender, s.Age
FROM   StudentTbl s
       CROSS APPLY
       (
            SELECT ',' + x.Class
            FROM    StudentTbl x
            WHERE   x.StudentId = s.StudentId
            FOR XML PATH ('')
       ) c (class)

#3

Thanks khtan. What is FOR XML PATH ('') purposes in the query for?

I tried. So instead taking just the dual records and combine those Class (Tony T445STD A,G M 22), the result set actually combine all classes
Tony T445STD A,A,A,A M 22
Tony T445STD G,G,G,G M 22
Bryan T200STD B,B,B,B M 24
Jessica T323STD C,C,C,C F 21

Thanks Kthan


#4

Thanks khtan!