multiple rows returned - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

multiple rows returned

Author

Topic

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 05:13:36

Having a slight problem with the layout of a datagrid
below is the select statement i am using to populate it

SELECT studentId, classId, studentName, unitId, grade, prelimDecisionCode, finalLetterIssued, finalDecisionCode, prelimLetterIssued
FROM qryStudentMasterResult
ORDER BY studentName

the problem is i am getting multiple rows ie:

123 1 jon 12 D I Yes I Yes
123 1 jon 13 A I Yes I Yes
123 1 jon 14 B I Yes I Yes

i would like the display as follows

123 1 jon D A B I Yes I Yes

any help would be great this has been bugging me for ages
please keep it as simple as possible as im new to SQL
many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 05:17:17

See this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75422

Peter Larsson
Helsingborg, Sweden

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 06:00:23

all a bit over my head, is there not an easier way??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 06:11:08

THE easiest way is to create a function! Or are you talking about a CROSS TAB / PIVOT report?

-- prepare test data
create table tmp (col1 int, col2 int, col3 varchar(3), col4 int, col5 varchar(1), col6 varchar(1), col7 varchar(3), col8 varchar(1), col9 varchar(3))

insert	tmp
select	123, 1, 'jon', 12, 'D', 'I', 'Yes', 'I', 'Yes' union all
select	123, 1, 'jon', 13, 'A', 'I', 'Yes', 'I', 'Yes' union all
select	123, 1, 'jon', 14, 'B', 'I', 'Yes', 'I', 'Yes'

select * from tmp

select	col1, col2, col3, dbo.fnConcat(col1) col4, col6, col7, col8, col9
from	tmp

drop table tmp

Function code found here

CREATE FUNCTION	dbo.fnConcat
(
	@Col1 INT
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE @s VARCHAR(8000)
	
	SELECT		@s = LEFT(ISNULL(@s + ' ', '') + Col5, 8000)
	FROM		tmp
	WHERE		Col1 = @Col1
	ORDER BY	Col4

	RETURN	@s
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 06:14:38

[code]-- prepare test data
create table tmp (col1 int, col2 int, col3 varchar(3), col4 int, col5 varchar(1), col6 varchar(1), col7 varchar(3), col8 varchar(1), col9 varchar(3))

insert tmp
select 123, 1, 'jon', 12, 'D', 'I', 'Yes', 'I', 'Yes' union all
select 123, 1, 'jon', 13, 'A', 'I', 'Yes', 'I', 'Yes' union all
select 123, 1, 'jon', 14, 'B', 'I', 'Yes', 'I', 'Yes'

select * from tmp

select col1,
col2,
col3,
max(case when col4 = 12 then col5 end) col5a,
max(case when col4 = 13 then col5 end) col5b,
max(case when col4 = 14 then col5 end) col5c,
col6,
col7,
col8,
col9
from tmp
group by col1,
col2,
col3,
col6,
col7,
col8,
col9

drop table tmp[/code]

Peter Larsson
Helsingborg, Sweden

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 06:40:30

completely lost but thanks anway

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 06:52:41

[code]ALTER FUNCTION dbo.fnConcat
(
@StudentID INT
)
RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @s VARCHAR(8000)

SELECT @s = LEFT(ISNULL(@s + ' ', '') + Grade, 8000)
FROM qryStudentMasterResult
WHERE Sudent = @StudentID
ORDER BY Grade DESC

RETURN @s
END
GO

SELECT DISTINCT studentId,
classId,
studentName,
dbo.fnConcat(studentid) grade,
prelimDecisionCode,
finalLetterIssued,
finalDecisionCode,
prelimLetterIssued
FROM qryStudentMasterResult
ORDER BY studentName
GO[/code]

Peter Larsson
Helsingborg, Sweden

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 06:55:59

will the select distinct work two grades could be the same?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 06:58:12

Yes!
The grades are concatenated in the function. The DISTINCT is only for remove duplicate rows in the ending resultset.

Peter Larsson
Helsingborg, Sweden

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 07:06:42

sorry very new to this, need to read up on sql. so this is a stored proc and i can call it using ado.net by the name dbo.fnConcat ?

studentId is a string do i just change it at the top of the function or is there any other changes required?

one more thing i need this function to return the results of a class using classId int

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-27 : 07:33:40

>> i can call it using ado.net by the name dbo.fnConcat ?

Yes. provided that it is part of the select statement

Madhivanan

Failing to plan is Planning to fail

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 07:37:43

sorry im a fool this is a function! right ? but still unsure on how to call it from vs2005.
also when i try to create the function im getting an error 208 invalid object name dbo.fnConcat.
im using sql2000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-27 : 07:43:54

>>when i try to create the function im getting an error 208 invalid object name dbo.fnConcat.

How can you get this error at the time of creation?
Make sure you created at the relevent database

Madhivanan

Failing to plan is Planning to fail

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 07:46:56

yes database is correct. right clicked in user defined functions, pasted in the function and then checked syntax and recieved the above error!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 08:16:43

May be you copied ALTER FUNCTION as posted by Peter.

Use CREATE FUNCTION STATEMENT when you are creating function for the first time:

CREATE FUNCTION	dbo.fnConcat
(...

Harsh Athalye
India.
"Nothing is Impossible"

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 11:43:27

here is the function now!

CREATE FUNCTION dbo.fnConcat

(
@StudentID NVARCHAR(50)
)
RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @s VARCHAR(8000)

SELECT @s = LEFT(ISNULL(@s + ' ', '') + Grade, 8000)
FROM qryStudentMasterResult
WHERE studentId = @StudentID
ORDER BY Grade DESC

RETURN @s
END
GO

SELECT DISTINCT studentId,
classId,
studentName,
dbo.fnConcat(studentid) grade,
prelimDecisionCode,
finalLetterIssued,
finalDecisionCode,
prelimLetterIssued
FROM qryStudentMasterResult
ORDER BY studentName
GO

syntax check is fine, but when i click ok im getting an error message 2714 there is already an object named fnConcat in the database?

and any help on how i call this function from vs2005 using vb would be great. i want to display the results in a datagrid. thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 12:23:33

If the object already exists, you must use ALTER FUNCTION to write the new function code to the database.

Peter Larsson
Helsingborg, Sweden

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 13:06:15

tried that now im getting a new error!

error:212 expression result length exceeds the maxium 8000 max,16000 found

do i just change the length of the return value, to what?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 13:09:29

It means that for a student, there are at least 4000 grades.

Peter Larsson
Helsingborg, Sweden

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 13:20:01

why would i get this error? there are only a few records in the db, as the project is in development!

jonjoe
Starting Member

14 Posts

Posted - 2006-11-27 : 13:21:17

do you mean a student can have a maximum of 4000 grades?