Solved

I need help with iterating the records of a database table and populating a field with the concatenation of 2 fields in the table.

Posted on 2008-06-16
4
513 Views
Last Modified: 2013-11-26
I currently have a table with the Following fields and records:

Class_ID, Class_Day, Start_Time, End_Time
"GMS-557-01","T","08:30AM","10:50AM"
"GMS-557-01","TH","08:30AM","10:50AM"
"GMS-595Y-01","T","1:00PM","2:50PM"
"GMS-610-01","M","08:30AM","11:50PM"
"GMS-623-01","M","01:00PM","05:50PM"
"GMS-623-01","W","03:00PM","04:50PM"
"GMS-623-01","F","01:00PM","02:30PM"
"GMS-649-01","W","08:00AM","09:50AM"
"GMS-649-01","F","08:00AM","09:50AM"
"GMS-710-01","TBA","08:00AM","09:50AM"

After running a stored procedure preferably or a vb2005 sub procedure in the code of my asp.net 2.0 project I need to have the day(s) that each class meets concatenated and entered in the Class_Days field.

Class_ID, Class_Day, Start_Time, End_Time, Class_Days
"GMS-557-01","T","08:30AM","10:50AM","T,TH"
"GMS-557-01","TH","08:30AM","10:50AM","T,TH"
"GMS-595Y-01","T","1:00PM","2:50PM","T"
"GMS-610-01","M","08:30AM","11:50PM","M"
"GMS-623-01","M","01:00PM","05:50PM","M,W,F"
"GMS-623-01","W","03:00PM","04:50PM","M,W,F"
"GMS-623-01","F","01:00PM","02:30PM","M,W,F"
"GMS-649-01","W","08:00AM","09:50AM",,"W,F"
"GMS-649-01","F","08:00AM","09:50AM",,"W,F"
"GMS-710-01","TBA","08:00AM","09:50AM","TBA"

Your help is greatly appreciated!
0
Comment
Question by:RosePat
  • 2
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 21796298
create UDF function like in example below (just change table name t1 to yours):


 

create table t1 (Class_ID varchar(50), Class_Day varchar(50), Start_Time varchar(50), End_Time varchar(50))

insert into t1

select 'GMS-557-01','T','08:30AM','10:50AM'

union all

select 'GMS-557-01','TH','08:30AM','10:50AM'

union all

select 'GMS-595Y-01','T','1:00PM','2:50PM'

union all

select 'GMS-610-01','M','08:30AM','11:50PM'

union all

select 'GMS-623-01','M','01:00PM','05:50PM'

union all

select 'GMS-623-01','W','03:00PM','04:50PM'

union all

select 'GMS-623-01','F','01:00PM','02:30PM'

union all

select 'GMS-649-01','W','08:00AM','09:50AM'

union all

select 'GMS-649-01','F','08:00AM','09:50AM'

union all

select 'GMS-710-01','TBA','08:00AM','09:50AM'
 

go

--create UDF 

create   FUNCTION  udf_Class_Days (@Class_ID varchar(30))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @strOutput VARCHAR(8000)
 

SELECT @strOutput =	
 
 

	COALESCE(@strOutput + ',','') +Class_Day	

                

	FROM	dbo. t1

	WHERE	[Class_ID] = @Class_ID

	
 

	RETURN @strOutput

end
 

GO

-----------------

-- Test code:
 

select *,dbo.udf_Class_Days(Class_ID) from t1

Open in new window

0
 

Author Comment

by:RosePat
ID: 21796920
Thank you so much EugeneZ, this is great!  You well deserve your rank!
Here is my test and the results I got:
My test:

select * ,dbo.udf_Class_Day('GMS-623-01') as ALL_Days from MyClass
where Class_ID =N'GMS-623-01'

GMS-623-01      M      01:00PM      05:50PM      NULL      M,W,F
GMS-623-01      W      03:00PM      04:50PM      NULL      M,W,F
GMS-623-01      F      01:00PM      02:30PM      NULL      M,W,F
Thanks so much!!!
0
 

Author Closing Comment

by:RosePat
ID: 31467704
EugeneZ, this is the 1st time I post a question.  You have no idea how much your answer will help me and my project. Thank you so much EugeneZ. You well deserve your rank!
Here is my test and the results I got:
select * ,dbo.udf_Class_Day('GMS-623-01') as ALL_Days from MyClass
where Class_ID =N'GMS-623-01'

GMS-623-01      M      01:00PM      05:50PM      NULL      M,W,F
GMS-623-01      W      03:00PM      04:50PM      NULL      M,W,F
GMS-623-01      F      01:00PM      02:30PM      NULL      M,W,F
Thanks you so much again!!!
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 21798506
you are very welcome :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now