I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem
You will get nowhere until you normalize your tables!!!
Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)
=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus
Room
ID Room
1 101
2 102
3 201
4 202
Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F
TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20
ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================
BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:
ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA
CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]
Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||
donni100 wrote:
Do you think I could accamplish here?
I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.
Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.
If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||
Give this a shot.
--First dump the result set into the first temp table (#temp1)
CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))
SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.
DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1
OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time
set @.DayOfWeek = ''
--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end
insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room
Drop table #temp2
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time
END
--cleraning up the cursor
CLOSE cur_room_time
DEALLOCATE cur_room_time
--selecting the final result set
Select *
from #temp3
DROP TABLE #temp3
Hope this works for you!
Ron N
|||It should work. Thanks a lot for help!|||i belive this can help
well use this function to get retrive the string that cotaians the rows of the specific ID.
CREATEFUNCTION dbo.ConRow(@.JID int)
RETURNSVARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SELECT @.Output =COALESCE(@.Output+', ','')+CONVERT(varchar(20), JP.a)
FROM [E_JobPending] JP
WHERE JP.JobID = @.JID
RETURN @.Output
END
select dbo.ConRow(jobid), vE_Job.*
from
vE_Job
DROPFUNCTION dbo.ConRow
|||Untested, but should give a single row
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]
Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:
SELECT pt.Building, pt.Time, pt."Room #"
, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek
FROM (
SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"
, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq
FROM tbl as t
) AS t1
PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt
You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.
donni100 wrote: