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

1 solution

573 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!

Comment

Question by:RosePat

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 43

Accepted Solution

by:Eugene Z

Eugene Z earned 500 total points

ID: 217962982008-06-16

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

Author Comment

by:RosePat

ID: 217969202008-06-16

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!!!

Author Closing Comment

by:RosePat

ID: 314677042008-06-16

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!!!

LVL 43

Expert Comment

by:Eugene Z

ID: 217985062008-06-16

you are very welcome :)

Featured Post

Question has a verified solution.

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

5 Lessons the Delta Outage Should Teach Us About Datacenter Security and Disaster Prevention

Article by: Concerto Cloud

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…

Understand What is Row Level Security in SQL Server 2016

Article by: Daniel

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works