Cross tab - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cross tab

Author

Topic

sureshmanian
Starting Member

26 Posts

Posted - 2012-05-09 : 10:09:34

Hi
I have a table with the following fields
(Day,Start,End,Activity)

Sample Data:
Day Start End Activity
Mon 9:00 10:30 Games
Mon 11:00 11:30 Dance
Mon 13:00 14:30 Recreation
Tue 9:30 11:00 Games
Tue 11:30 13:30 Lab
Tue 14:00 15:30 Lecture

I am looking for the output:

Day 9:00-10:00 10:00 - 11:00 11:00 - 12:00 12:00-13:00 13:00-
--------------------------------------------------------------------
Mon Games Games Dance Recrea

Tue Games Games Lab Lab Lab

Wed

Thu

Fri

Sat

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

Thanks for your help.

Rgds
SSM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 15:56:13

what if you'd multiple activities within single hour slot? say 10:00 - 10:30 and 10:30 -11:00
in such cases what should be output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sureshmanian
Starting Member

26 Posts

Posted - 2012-05-10 : 02:08:45

I didn't think about it, however, in that case it has to display the multiple activities in that column.
Thanks for your reply.
Rgds
SSM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 12:07:16

are Start and End columns of time datatype?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sureshmanian
Starting Member

26 Posts

Posted - 2012-05-11 : 08:05:56

@Visakh 16
Yes, Start and End columns are time datatype.
Thanks for your reply.
Regards
SSM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:15:02

[code]
;With TimeSlots(Start,End)
AS
(
SELECT CAST('09:00' AS time),CAST('10:00' AS time)
UNION ALL
SELECT DATEADD(hh,1,Start),DATEADD(hh,1,End)
FROM TimeSlots
WHERE DATEADD(hh,1,Start) <=CAST('23:00' AS time)
),
Result
AS
(
SELECT m.Day,
CAST(m.Start AS varchar(5)) + ' - ' + CAST(m.End AS varchar(5)) AS Slot,
STUFF((SELECT ',' + Activity
FROM Table n
WHERE Day = m.Day
AND Start >= m.Start
AND Start < m.End
AND End > m.Start
FOR XML PATH('')
),1,1,'')

FROM
(
SELECT Day,
Start,
End
FROM TimeSlots t
CROSS JOIN (SELECT DISTINCT Day FROM Table)t
)m
)

SELECT Day,[9:00-10:00], [10:00 - 11:00], [11:00 - 12:00],...
FROM Results
PIVOT (MAX(Activity) FOR Slot IN ([9:00-10:00], [10:00 - 11:00], [11:00 - 12:00],...))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources