SQL query to get data in a specific format

Hi

I have a table with bookings in, they have a date and time, but will always be am/pm bookings.

I need to display the data as per the attached grid view image (the screen shot was done with a test static dataset).

How can I best transform my data into this shape? I'm using MS SQL 2008.

Table:
CREATE TABLE [dbo].[Bookings] (
   [Id] [int] NULL,
   [Name] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL,
   [Owner] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL,
   [Location_Id] [int] NULL,
   [Location] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL,
   [Start] [datetime] NULL,
   [End] [datetime] NULL
)
GO

Insert demo data:


INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(7,N'Test meeting 1 (8-1)',N'Site Administrator',140,N'Meeting Room 1','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(8,N'Test meeting 1 (8-1)',N'Site Administrator',140,N'Meeting Room 1','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(17,N'Test meeting 1 (8-1)',N'Site Administrator',89,N'Meeting Room 2','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(18,N'Test meeting 1 (8-1)',N'Site Administrator',89,N'Meeting Room 2','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(27,N'Test meeting 1 (8-1)',N'Site Administrator',92,N'Meeting Room 3','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(28,N'Test meeting 1 (8-1)',N'Site Administrator',92,N'Meeting Room 3','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(37,N'Test meeting 1 (8-1)',N'Site Administrator',136,N'Meeting Room 4','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(38,N'Test meeting 1 (8-1)',N'Site Administrator',136,N'Meeting Room 4','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(47,N'Test meeting 1 (8-1)',N'Site Administrator',137,N'Meeting Room 5','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(48,N'Test meeting 1 (8-1)',N'Site Administrator',137,N'Meeting Room 5','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(57,N'Test meeting 1 (8-1)',N'Site Administrator',138,N'Meeting Room 6','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(58,N'Test meeting 1 (8-1)',N'Site Administrator',138,N'Meeting Room 6','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(67,N'Test meeting 1 (8-1)',N'Site Administrator',139,N'Meeting Room 7','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(68,N'Test meeting 1 (8-1)',N'Site Administrator',139,N'Meeting Room 7','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(77,N'Test meeting 1 (8-1)',N'Site Administrator',84,N'Meeting Room 8','2018-01-19 08:30:00.000','2018-01-19 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(78,N'Test meeting 1 (8-1)',N'Site Administrator',84,N'Meeting Room 8','2018-01-20 08:30:00.000','2018-01-20 13:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(87,N'Test meeting 2 (1-6)',N'Site Administrator',140,N'Meeting Room 1','2018-01-19 14:00:00.000','2018-01-19 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(88,N'Test meeting 2 (1-6)',N'Site Administrator',140,N'Meeting Room 1','2018-01-20 14:00:00.000','2018-01-20 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(97,N'Test meeting 2 (1-6)',N'Site Administrator',89,N'Meeting Room 2','2018-01-19 14:00:00.000','2018-01-19 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(98,N'Test meeting 2 (1-6)',N'Site Administrator',89,N'Meeting Room 2','2018-01-20 14:00:00.000','2018-01-20 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(107,N'Test meeting 2 (1-6)',N'Site Administrator',92,N'Meeting Room 3','2018-01-19 14:00:00.000','2018-01-19 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(108,N'Test meeting 2 (1-6)',N'Site Administrator',92,N'Meeting Room 3','2018-01-20 14:00:00.000','2018-01-20 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(117,N'Test meeting 2 (1-6)',N'Site Administrator',136,N'Meeting Room 4','2018-01-19 14:00:00.000','2018-01-19 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(118,N'Test meeting 2 (1-6)',N'Site Administrator',136,N'Meeting Room 4','2018-01-20 14:00:00.000','2018-01-20 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(127,N'Test meeting 2 (1-6)',N'Site Administrator',137,N'Meeting Room 5','2018-01-19 14:00:00.000','2018-01-19 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(128,N'Test meeting 2 (1-6)',N'Site Administrator',137,N'Meeting Room 5','2018-01-20 14:00:00.000','2018-01-20 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(137,N'Test meeting 2 (1-6)',N'Site Administrator',138,N'Meeting Room 6','2018-01-19 14:00:00.000','2018-01-19 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(138,N'Test meeting 2 (1-6)',N'Site Administrator',138,N'Meeting Room 6','2018-01-20 14:00:00.000','2018-01-20 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(147,N'Test meeting 2 (1-6)',N'Site Administrator',139,N'Meeting Room 7','2018-01-19 14:00:00.000','2018-01-19 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(148,N'Test meeting 2 (1-6)',N'Site Administrator',139,N'Meeting Room 7','2018-01-20 14:00:00.000','2018-01-20 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(157,N'Test meeting 2 (1-6)',N'Site Administrator',84,N'Meeting Room 8','2018-01-19 14:00:00.000','2018-01-19 18:30:00.000')
INSERT INTO [dbo].[Bookings] ([Id],[Name],[Owner],[Location_Id],[Location],[Start],[End]) VALUES(158,N'Test meeting 2 (1-6)',N'Site Administrator',84,N'Meeting Room 8','2018-01-20 14:00:00.000','2018-01-20 18:30:00.000')
GO

This is my demo fixed data set just so yo ucan see how I got the look I'm after:
Select 1 ID, 'Room 1' Room, '08:00-13:00 Dr Adiyodi' Monday, '08:00-13:00 Dr Adiyodi' Tuesday, '08:00-13:00 Dr Adiyodi' Wednesday, '08:00-13:00 Dr Adiyodi' Thursday, '08:00-13:00 Dr Adiyodi' Friday
Union
Select 2 ID, 'Room 1' Room, '14:00-17:00 Dr Durgam' Monday, '14:00-17:00 Dr Durgam' Tuesday, '14:00-17:00 Dr Durgam' Wednesday, '14:00-17:00 Dr Durgam' Thursday, '14:00-17:00 Dr Durgam' Friday
Union
Select 3 ID, 'Room 2' Room, '08:00-13:00 Dr Adiyodi' Monday, '08:00-13:00 Dr Adiyodi' Tuesday, '08:00-13:00 Dr Adiyodi' Wednesday, '08:00-13:00 Dr Adiyodi' Thursday, '08:00-13:00 Dr Adiyodi' Friday
Union
Select 4 ID, 'Room 2' Room, '' Monday, '14:00-17:00 Dr Durgam' Tuesday, '14:00-17:00 Dr Durgam' Wednesday, '14:00-17:00 Dr Durgam' Thursday, '14:00-17:00 Dr Durgam' Friday
Union
Select 5 ID, 'Room 3' Room, '08:00-13:00 Dr Adiyodi' Monday, '08:00-13:00 Dr Adiyodi' Tuesday, '' Wednesday, '08:00-13:00 Dr Adiyodi' Thursday, '08:00-13:00 Dr Adiyodi' Friday
Union
Select 6 ID, 'Room 3' Room, '14:00-17:00 Dr Durgam<br/>16:00-17:00 Dr Smith' Monday, '14:00-17:00 Dr Durgam' Tuesday, '14:00-17:00 Dr Durgam' Wednesday, '14:00-17:00 Dr Durgam' Thursday, '14:00-17:00 Dr Durgam' Friday
Union
Select 7 ID, 'Room 4' Room, '08:00-13:00 Dr Adiyodi' Monday, '08:00-13:00 Dr Adiyodi' Tuesday, '08:00-13:00 Dr Adiyodi' Wednesday, '08:00-13:00 Dr Adiyodi' Thursday, '08:00-13:00 Dr Adiyodi' Friday
Union
Select 8 ID, 'Room 4' Room, '14:00-17:00 Dr Durgam' Monday, '14:00-17:00 Dr Durgam' Tuesday, '14:00-17:00 Dr Durgam' Wednesday, '14:00-17:00 Dr Durgam' Thursday, '14:00-17:00 Dr Durgam' Friday
Union
Select 9 ID, 'Room 1' Room, '14:00-17:00 Dr Durgam' Monday, '14:00-17:00 Dr Durgam' Tuesday, '14:00-17:00 Dr Durgam' Wednesday, '14:00-17:00 Dr Durgam' Thursday, '14:00-17:00 Dr Durgam' Friday

Order By Room


Any help / pointers greatly appreciated.

Andy
Capture.PNG
LVL 3
Andy GreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ManjuIT - Project ManagerCommented:
Something like this...,

Select Distinct Location as Room, 

Case when DateName(weekday, Start) = 'Monday' Then
((convert(varchar(5), Start, 108) + ' - ' + convert(varchar(5), [End], 108) ) + ' ' + Name)
end as Monday,

Case when DateName(weekday, Start) = 'Tuesday' Then
((convert(varchar(5), Start, 108) + ' - ' + convert(varchar(5), [End], 108) ) + ' ' + Name)
end as Tuesday,

Case when DateName(weekday, Start) = 'Friday' Then
((convert(varchar(5), Start, 108) + ' - ' + convert(varchar(5), [End], 108) ) + ' ' + Name)
end as Friday,

Case when DateName(weekday, Start) = 'Saturday' Then
((convert(varchar(5), Start, 108) + ' - ' + convert(varchar(5), [End], 108) ) + ' ' + Name)
end as Saturday

from Bookings Group by Location, Name, Start, [End]

Open in new window

1
Andy GreenAuthor Commented:
Thanks - Nearly.

This puts each booking on a separate row, I need all the days on the same row for the same location / time.

Andy
Capture1.PNG
0
Mark WillsTopic AdvisorCommented:
First of all, I want to thank you for such a well constructed question

You gave us table, data and expected outcomes / results

Thank you.


Will be back later to contribute.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WillsTopic AdvisorCommented:
To me, it looks like a classic pivot. Have a look at :

Now, I did add Week to the output because I added in some extra data to get a more even spread....
The week probably needs more work

Select Week
       , Location
       , isnull([Monday],'') [Monday]
       , isnull([Tuesday],'') [Tuesday]
       , isnull([Wednesday],'') [Wednesday]
       , isnull([Thursday],'') [Thursday] 
       , isnull([Friday],'') [Friday]
       , isnull([Saturday],'') [Saturday]
       , isnull([Sunday],'') [Sunday]
from 
     ( select datename(week,Start) as week
              , Location
              , convert(varchar(8), Start, 108) + ' - ' + convert(varchar(8), [End], 108) as MeetingTime
              , datename(weekday,Start) as WD
              , ((convert(varchar(5), Start, 108) + ' - ' + convert(varchar(5), [End], 108) ) + ' ' + Name) as BookingDetail 
       from bookings 
     ) s
PIVOT
     ( max(BookingDetail) for WD in ([Monday],[Tuesday],[Wednesday], [Thursday], [Friday], [saturday],[Sunday])
     ) p
Order by 1,2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andy GreenAuthor Commented:
Thank you Mark for your kind words and solution.

It took me 5 mins to put the test script together and making it easier for the experts makes it easy for me. I mean explaining the problem re enforces my understanding too.

I have it working now. Just expanding to include the bookingID so I have a click to edit value.

Andy
1
Andy GreenAuthor Commented:
As per last comment. Thank you both, Mark was the solution I went with.

Andy
0
Mark WillsTopic AdvisorCommented:
Andy,

An absolute pleasure. Enjoyed working with your requirements. It does make such a big difference :)

Regards,
Mark Wills
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.