Summary: multiple rows into one

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

SQL Server Development (2000)

Summary: multiple rows into one

Author

Topic

rafreid
Starting Member

3 Posts

Posted - 2005-10-07 : 12:24:55

I am having a hard time with the following.
I would like to summarize into one row,
entries that have same start,stop time & room
number. For example registration 2576 meets
on days(1,2,3) but days 3 and 1 are at the same
time and in same room. So I need to have only
one row for day 1,3.

Can anyone help ?

Thanks --

registration table
--- code day starttime stoptime room
---------------------------------------------
--> 2576 1 930 1045 1307
--- 2576 2 1400 1750 1332
--> 2576 3 930 1045 1307

what I need:

--- code day starttime stoptime room
---------------------------------------------
--> 2576 13 930 1045 1307
--- 2576 2 1400 1750 1332

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-07 : 12:27:23

That looks like Day 13 now, doesn't it.

Why do you want to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rafreid
Starting Member

3 Posts

Posted - 2005-10-07 : 12:42:01

Brett,

Thanks for your quick reply.
"13" is a string - the application I am maintaining
will read it as Monday,Wednesday-

ralph --

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-07 : 12:59:09

Happy Birthday


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Code char(4), [day] char(1), starttime char(4), stoptime char(4), room char(4))
GO

INSERT INTO myTable99(code, [day], starttime, stoptime, room)
SELECT '2576', '1', '930',  '1045', '1307' UNION ALL 
SELECT '2576', '2', '1400', '1750', '1332' UNION ALL 
SELECT '2576', '3', '930',  '1045', '1307' 
GO

CREATE FUNCTION GetAllOnLine2(@code char(4), @starttime char(4), @stoptime char(4), @room char(4))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)

SELECT @Result = COALESCE(@Result + ', ','') + [day]
FROM myTable99
WHERE code = @code AND starttime = @starttime AND stoptime = @stoptime AND room = @room

RETURN @Result
END
GO

SELECT DISTINCT    
	  code
	, starttime
	, stoptime
	, room
	, dbo.GetAllOnLine2(code, starttime, stoptime, room) AS Days 
  FROM MyTable99
GO

SET NOCOUNT OFF
DROP FUNCTION GetAllOnLine2
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rafreid
Starting Member

3 Posts

Posted - 2005-10-07 : 13:06:25

amazing...

Thanks a lot -

Ralph --

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-07 : 13:10:16

Hey, Ralph, you're from THE City...where abouts? Who do you work for?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 01:52:37

Refer this also to know why Function is more efficient

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources