SQL 2008 Query to produce a report

Dear Experts,

This should be simple, but I’m having a brain dead day.  A day where you leave your coffee on top of the car and drive off.  I want to produce a query where the results show on each row the StudId in one column and his Scores in the second columns separated by a comma.

StudId   Scores
1            81,92,99,65,78,56
2            83,89,43,72,65,77
35         98,96,83

I have attached code to create the test table and insert the raw data.
INSERT-INTO-Scores.sql
CREATE-SCORES.sql
wdbatesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT [StudId], SUBSTRING(
 (SELECT ',' + cast (s.[Score] AS VARCHAR(20) )
FROM dbo.Scores s
WHERE s.studID = o.studid
ORDER BY s.[Score]
FOR XML PATH('')),2,200000) AS CSV
FROM dbo.Scores o
GROUP by studID
 GO
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can I just say "thank you". So few people actually give us a sample data and sample structure. So:

Thank you
Thank you
Thank you
Thank you
Thank you
Thank you

I'm be back in a minute.
0
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
select StudID, 
STUFF((select ', ' + CONVERT(VARCHAR(10),[Score]) from dbo.Scores as T
where T.StudID = S.StudID
FOR XML PATH ('')),1,3,'') AS Scores
FROM dbo.Scores as S
group by StudID

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Ahhhh! 6 seconds!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
@Philip, I waited for 5minutes before I posted my solution, I thought you became busy  after all we all have full time jobs..
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Phillip lost time thanking for the samples :)
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It's true!
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
But it was worth it. How much easier does it make your life?
0
 
wdbatesAuthor Commented:
You guys are great!  I'm going half and half.  Aneesh, you were first, but Phillip through in STUFF()  which is something new for me.  I always like to learn something new.
0
 
wdbatesAuthor Commented:
Phillip,  Just noticed that your answer was not correct.  Where did the score of 1 come from StudId 1, and 3 from StudId 2 and the score of 8 from StudId35?  Let me know what you discover.  Thanks!

StudID      Scores
1             1, 92, 99, 65, 78, 56
2             3, 89, 43, 72, 65, 77
35             8, 96, 83

Correct answer:
StudId      CSV
1             56,65,78,81,92,99
2             43,65,72,77,83,89
35             83,96,98
0
 
wdbatesAuthor Commented:
Phillip, Had a chance to go back and look at your query and changed the STUFF value ,1,3,'' to 1,2,''.
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.