Last post Nov 20, 2014 11:19 AM by limno
Nov 18, 2014 02:04 PM|tompark|LINK
I have a bit of a complicated issue that I’m looking for a few suggestion on. I have to display an entire TimeTable in a web page, but I’m not sure on the best way to go about it.
I have tables that have the following data
In the web page I would like to show something like this:
I was thing about doing it using repeaters so get the start and end times of classes and then after that put the relevant data into the table after that based on the day.
I hope this makes sense on what I am trying to Achieve.
Nov 20, 2014 12:50 AM|Michelle Ge - MSFT|LINK
According to your description, you need to convert the rows to colum by the function PIVOT(). I created a sql query, please refer to the code below:
Create the Table:
CREATE TABLE [dbo].[TimeTable] (
[WeekID] INT NOT NULL,
[StutID] INT NOT NULL,
[StartTime] TIME (7) NOT NULL,
[EndTime] TIME (7) NOT NULL,
[Day] INT NOT NULL,
[ClassID] NCHAR (10) NOT NULL
The sql query:
select Day, [09:00:00-10:00:00],[10:00:00-11:00:00] ,[11:20:00-12:20:00] ,[12:20:00-13:20:00] ,[13:55:00-14:55:00] from
(select (convert(nvarchar(10),StartTime,108)+'~'+convert(nvarchar(10),EndTime,108)) as TimeRange,Day,ClassID from TimeTable) T
For T.TimeRange in ([09:00:00-10:00:00],[10:00:00-11:00:00],[11:20:00-12:20:00],[12:20:00-13:20:00],[13:55:00-14:55:00])
) as PivotTable;
For more information about PIVOT function, please refer to the link below:
Hope it's useful for you.
Nov 20, 2014 11:19 AM|limno|LINK
You can learn how to use dynamic sql to get what you want. Here is a sample query for your question. You can run it in SSMS to test it out.
create table timetables (TimeTable int, StudID int,StartTime time, EndTime time, [Day] int, ClassID varchar(10))
Insert into timetables values (1,5588,'10:00','11:00',1,'A1'),(1,5588,'11:20','12:20',1,'B1'),(1,5588,'09:00','10:00',2,'A1'),(1,5588,'10:00','11:00',2,'C1')
DECLARE @col AS VARCHAR(8000)=''
DECLARE @sql AS VARCHAR(8000)=''
SELECT @col=stuff( (SELECT ',['+ Cast(StartTime as varchar(5)) +' - '+ Cast(EndTime as varchar(5)) +']'
FROM (select distinct StartTime,EndTime from timetables) p
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
SET @sql='SELECT [Day] , ' + @col
+ ' FROM (select [Day] ,ClassID, Cast(StartTime as varchar(5)) +'' - ''+ Cast(EndTime as varchar(5)) as details FROM timetables) src
PIVOT (MAX(ClassID ) FOR [Details] IN ('+ @col + ')) AS pvt'
drop table timetables