Link to home
Start Free TrialLog in
Avatar of Andy Green
Andy GreenFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Manju
Manju
Flag of India image
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

Avatar of Andy Green
Andy Green
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of Mark Wills
Mark Wills
Flag of Australia image
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.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andy Green
Andy Green
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of Andy Green
Andy Green
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

As per last comment. Thank you both, Mark was the solution I went with.

Andy
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Andy,

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

Regards,
Mark Wills