SQLTeam.com | Weblogs | Forums

Multiple rows to single row


#1

Hello SQL expert,

Below are my sql query and the result set.
I am trying to make my result set to single row for every Student and combine their scores.

My query:

SELECT DISTINCT Student_Name, Student_ID, Class, Score
FROM StudentTbl

Default result set:

Student_Name Student_Id Class Score
Sabrina Davis SDT2354 A 75
Sabrina Davis SDT2354 A 60
Sabrina Davis SDT2354 A 89
Andy Hills SDT3588 F 99
Steve Collins SDT3354 G 100
Steve Collins SDT3354 G 95
Steve Collins SDT3354 G 80
Amy Dillon SDT3567 T 77
Amy Dillon SDT3567 T 80
Amy Dillon SDT3567 T 91

Result I am trying to achieve:
Student_Name Student_Id Class Score
Sabrina Davis SDT2354 A 75,60,89
Andy Hills SDT3588 F 99
Steve Collins SDT3354 G 100,95,80
Amy Dillon SDT3567 T 77,80,91

How do I update my above query? Anyone? Thanks all!


#2
SELECT
	a.Student_Name,
	a.Student_Id,
	a.Class,
	STUFF(b.Score,1,1,'') AS Score
FROM
	(
		SELECT DISTINCT Student_Name, Student_Id, Class 
		FROM YourTable 
	) AS a
	CROSS APPLY
	(
		SELECT ','+CAST(Score AS VARCHAR(32))
		FROM
			YourTable b
		WHERE
			b.Student_Id = a.Student_Id
			AND b.Class = a.Class
		FOR
			XML PATH('')
	) b(Score);

#3

Thanks JamesK. You are a sql genius!!!


#4

Hi James,

Your query works great but how do I eliminate the same value?
For example:

From:

Student_Name Student_Id Class Score
Andy Hills SDT3588 F 99,99,99,99

To:
Student_Name Student_Id Class Score
Andy Hills SDT3588 F 99

Thanks James


#5

This doesn't actually make sense anymore. Why would you want to eliminate duplicates for the CSV version of the class score? At least with the duplicates, you can derive a count of classes and requires no special handling.

The original problem also is close to not making sense in the world of databases. Why do you want a CSV version of all scores for an individual? In other words, how will such information actually be used? I ask not as a challenge but because if we knew the end goal, there may be a better way.


#6

Hi JeffModen,

The purposes of I am trying to achieve this is so we can only have distinct count on each Student. The above queries that James did was great. It works perfectlly! However, some student may have same scores on their test and in this scenario, we only care all the disctint scores.

So again for example if a student received these scores:

Student(s) : 77, 85, 92, 85, 65, 99, 77
We only want to show: 65, 77, 85, 92, 99

Thanks JeffModen


#7

I have the same concerns as @JeffModen regarding selecting only distinct values. If a student has two courses for which she had exactly the same score, you wouldn't want to eliminate one of those, would you? Nonetheless, I won't pretend to understand your business rules, so if you do want to eliminate duplicates, one way would be this:

SELECT
	a.Student_Name,
	a.Student_Id,
	a.Class,
	STUFF(c.Score,1,1,'') AS Score
FROM
	(
		SELECT DISTINCT Student_Name, Student_Id, Class 
		FROM YourTable 
	) AS a
	CROSS APPLY
	(
		SELECT DISTINCT b.Score
		FROM
			YourTable b
		WHERE
			b.Student_Id = a.Student_Id
			AND b.Class = a.Class
	) AS b
	CROSS APPLY
	(
		SELECT ','+CAST(Score AS VARCHAR(32))
		FROM
			b
		FOR
			XML PATH('')
	) c(Score);

#8

drop table #test

create table #test (
Student_Name varchar(255) ,
Student_Id varchar(255),
Class varchar(255) ,
score int
)

insert into #test values
('Sabrina Davis','SDT2354','A',75)
,('Sabrina Davis','SDT2354','A',60)
,('Sabrina Davis','SDT2354','A',89)
,('Andy Hills','SDT3588','F',99)
,('Steve Collins','SDT3354','G',100)
,('Steve Collins','SDT3354','G',95)
,('Steve Collins','SDT3354','G',80)
,('Amy Dillon','SDT3567','T',77)
,('Amy Dillon','SDT3567','T',80)
,('Amy Dillon','SDT3567','T',91)
,('Andy Hills','SDT3588','F',99)
,('Andy Hills','SDT3588','F',99)
,('Andy Hills','SDT3588','F',99)
,('Andy Hills','SDT3588','F',99)

-- your original
select * from #test

--JamesK solution

SELECT
a.Student_Name,
a.Student_Id,
a.Class,
STUFF(b.Score,1,1,'') AS Score
FROM
(
SELECT DISTINCT Student_Name, Student_Id, Class
FROM #test
) AS a
CROSS APPLY
(
SELECT ','+CAST(Score AS VARCHAR(32))
FROM
#test b
WHERE
b.Student_Id = a.Student_Id
AND b.Class = a.Class
FOR
XML PATH('')
) b(Score);

--Distinct students results

SELECT
a.Student_Name,
a.Student_Id,
a.Class,
STUFF(b.Score,1,1,'') AS Score
FROM
(
SELECT DISTINCT Student_Name, Student_Id, Class
FROM #test
) AS a
CROSS APPLY
(
--Add distinct here
SELECT distinct ','+CAST(Score AS VARCHAR(32))
FROM
#test b
WHERE
b.Student_Id = a.Student_Id
AND b.Class = a.Class
FOR
XML PATH('')
) b(Score);


#9

That's what I mean... your request is obvious but the reason for it is not. What is the business reason for wanting the distinct count of grades? You've explained the WHAT quite nicely but not the WHY. WHY is it important to only show distinct grades rather than all grades?


#10

Muj9. that works!!! Thanks again for helping.


#11

Hi JamesK,

Thanks for helping. You are the SQL genius !!!


#12

Hi JeffModen,

The database not developed properly so we have to handle this mess but using this solution. Anyway, we got it thru JamesK query and I just add the distinct per Muj9.

Thanks again Jeff.

Thanks all for all your help!!! I really appreciated!


#13

Understood... but it would have been nice to know what the "mess" is that you're trying to solve.