September 26, 2011 at 11:19 am
This should be simple (but maybe not) but I have a complete block!
Here is a simple example of what I need to accomplish:
A very simple table with 2 columns - room# and capacity. I want to query this table to return 1 row for each room capacity to link with a booking table.
So if Room 1 has a capacity of 1, Room 2 has a capacity of 2 and Room 3 has a capacity of 3, I want to return in the query results 1 row for room 1, 2 rows for room 2, and 3 rows for room 3. I can use this result set to link to a booking table to return a listing of all bookings and vacancies for a time period.
Thanks
CREATE TABLE [dbo].[test] (
[roomID] [int] IDENTITY(1, 1) NOT NULL,
[capacity] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test]
ADD
CONSTRAINT [PK_test]
PRIMARY KEY
CLUSTERED
([roomID])
ON [PRIMARY]
GO
SET NOCOUNT ON
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [dbo].[test] ON
INSERT INTO [dbo].[test] ([roomID], [capacity]) VALUES (1, 1)
INSERT INTO [dbo].[test] ([roomID], [capacity]) VALUES (2, 2)
INSERT INTO [dbo].[test] ([roomID], [capacity]) VALUES (3, 3)
SET IDENTITY_INSERT [dbo].[test] OFF
Tim
September 26, 2011 at 11:24 am
That sounds like really strange requirement but you can use a tally table pretty easily like this.
select *
from test
join Tally on Tally.N < = test.capacity
Refer to the article here[/url] by Jeff Moden to build a tally table.
I would be interested to hear the business rules for why you would your data returned like this.
You could add a RowNumber to this so you know "which" copy of the record you are on.
select test.roomID, ROW_NUMBER() over (partition by roomid order by roomid) as RowNum
from test
join Tally on Tally.N < = test.capacity
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2011 at 11:31 am
It is to return a data set that contains all bookings a vacancies for a youth residence for any time period (one head per bed), where the data model is only granular to the room level, not individual beds. Each room has a capacity (number of beds) and we have to return rows for the occupied beds (no problem there) and also a row for the empty beds (to display as available on the booking chart in the application).
Thanks for the quick response - I will check out the Tally table article and see if we can get that solution to apply.
Tim
September 26, 2011 at 11:39 am
The tally table is nothing more than a table with a single column of integers incrementally. The article has an easy way to create it. However, the article is a great read regardless. Jeff (the author) is a regular around here and a great writer. This article is referred to constantly as a solid solution to get a set based process for many types of challenges.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply