Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
636 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 43

Accepted Solution

by:
Eugene Z earned 2000 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 43

Expert Comment

by:Eugene Z
ID: 21798506
you are very welcome :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

578 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