Group by with multiple results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by with multiple results

Author

Topic

Big_tim
Starting Member

22 Posts

Posted - 2010-11-03 : 08:31:03

Hey, I'm sure there is a really simple solution for this, but just can't figure it out!
I'm using this script:

SELECT DISTINCT
	U.FirstName,
	U.LastName,
	LS.ScoreRaw

FROM (
	SELECT 
		UserID,
		LearningObjectID, 
		ScoreRaw
	FROM dbo.LearningSessions

	GROUP BY 
		UserID,
		LearningObjectID, 
		ScoreRaw ) LS
	INNER JOIN dbo.Users U ON LS.UserID = U.UserID
	INNER JOIN dbo.LMSRoles LMSR ON U.LMSRoleID = LMSR.LMSRoleID
	INNER JOIN dbo.LearningObjects LO ON LS.LearningObjectID = LO.LearningObjectID
	INNER JOIN dbo.LearningObjectCultures LOC ON LO.LearningObjectID = LOC.LearningObjectID
	INNER JOIN dbo.CourseLearningObjects CLO ON LO.LearningObjectID = CLO.LearningObjectID
	INNER JOIN dbo.Courses C ON CLO.CourseID = C.CourseID
	INNER JOIN dbo.CourseCategoryCultures CatCC ON C.CourseCategoryID = CatCC.CourseCategoryID
WHERE 
	((U.FirstName = 'Rebecca' AND U.LastName = 'Armstrong')
	OR	(U.FirstName = 'Craig' AND U.LastName = 'Armstrong'))	
	AND	CatCC.Title LIKE '%TI'
	AND LO.Code LIKE '%I_SCO0_SCO0'
	AND LOC.Title = 'Technical Integration pre-course learning'
GROUP BY
	U.FirstName,
	U.LastName,
	LS.ScoreRaw
ORDER BY 
	U.LastName,
	U.FirstName

Which gives me the results:

FirstName LastName ScoreRaw
Craig Armstrong NULL
Craig Armstrong 0
Rebecca Armstrong NULL
Rebecca Armstrong 0

However, I want it to give the results with the names grouped and score in 2 columns:

FirstName LastName ScoreRaw1 ScoreRaw2
Craig Armstrong NULL 0.0
Rebecca Armstrong NULL 0.0

How do I do that?!

Thanks in advance.

Sachin.Nand

2937 Posts

Posted - 2010-11-03 : 08:32:58

Are you sure there always will be just 2 scores for each user?

PBUH

Big_tim
Starting Member

22 Posts

Posted - 2010-11-03 : 08:34:57

No, there could be multiple results.

Sachin.Nand

2937 Posts

Posted - 2010-11-03 : 08:45:46

quote:
However, I want it to give the results with the names grouped and score in 2 columns:

Then does the above requirement still stands?

What if the resultset is this ?


FirstName LastName ScoreRaw
Craig Armstrong NULL
Craig Armstrong 0
Craig Armstrong 10
Craig Armstrong 15
Rebecca Armstrong NULL
Rebecca Armstrong 0
Rebecca Armstrong 5

What should be the o/p?

PBUH

Big_tim
Starting Member

22 Posts

Posted - 2010-11-03 : 08:58:09

Good point...
In that case, if a result was returning a null as above than it would have to translate it to something like 'incomplete'
Then in any other additional columns that it adds where a null wasn't returned in the original script, put a null in those.

So the above example you put would be:

FirstName LastName ScoreRaw1 ScoreRaw2 ScoreRaw3 ScoreRaw4
Craig Armstrong Incomplete 0 10 15
Rebecca Armstrong Incomplete 0 5 NULL

Sachin.Nand

2937 Posts

Posted - 2010-11-03 : 13:55:07

[code]
create table #tbl(FirstName varchar(20), LastName varchar(20), ScoreRaw int)

insert into #tbl

select 'Craig', 'Armstrong', 0 union

select 'Craig', 'Armstrong' ,5 union

select 'Craig', 'Armstrong' ,10 union

select 'Rebecca', 'Armstrong', 0 union

select 'Rebecca', 'Armstrong' ,0

declare @collist as varchar(max)=''

select @collist=@collist + rid from(
select ',[ScoreRaw' + convert(varchar(10),ROW_NUMBER()over(partition by firstname order by firstname)) + ']' rid from #tbl
)t group by rid

declare @sql as varchar(max)

select @sql='select * from

(

select FirstName,col,ScoreRaw from
(
select FirstName,''ScoreRaw'' + '''' + convert(varchar(10),ROW_NUMBER()over(partition by firstname order by firstname))col,ScoreRaw from #tbl
)t

)u pivot

(max(scoreraw) for col in(' + stuff(@collist,1,1,'') + '))v'

exec (@sql)

drop table #tbl

[/code]

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources