MS SQL Query Help

[greyhawkes]

I've inherited a MS SQL database that I can't change the design of it (and I hate it :-). I'm trying to get some data out of a table that has the following
columns (among others):

day_of_week int 4
open_time char 4
close_time char 4
sched_no int 4

day_of_week values are 1-7
open_time can be 0000-2359
close_time can be 0000-2359
sched_no can be 1-9

I need to figure out a query to get something like the following for a report:
Schedule 1: Open Close
Day 1 0730 1800
2 0730 1800
3 0730 1800
4 0730 1800
5 0730 1800
6 0300 1800
7 0730 1800
Scheduel 2: Open Close
1 0900 1900
2 0900 1900
etc.

Any help is much appreciated.

Be well,
Steve

[MAK]

create table schedule (
day_of_week int ,
open_time char(4),
close_time char(4),
sched_no int )
go
insert into schedule select 1, '0730', '1800', 1
insert into schedule select 2, '0730', '1800', 1
insert into schedule select 3, '0730', '1800', 1
insert into schedule select 4, '0730', '1800', 1
insert into schedule select 5, '0730', '1800', 1
insert into schedule select 6, '0300', '1800', 1
insert into schedule select 7, '0730', '1800', 1
insert into schedule select 1, '0900', '1900', 2
insert into schedule select 2, '0900', '1900', 2
go

--Query

select Details = case day_of_week when 1 then 'Schedule '+convert(varchar(4),sched_no) +': Open Close' +char(10)+char(13)+ Convert(varchar(4),day_of_week) +' '+open_time+' '+close_time else
Convert(varchar(4),day_of_week) +' '+open_time+' '+close_time end
from schedule