# How to dynamically generate rows based on value in a column

I'm attempting to write a query to denormalize some data for integrating to our data warehouse, specifically around generating some time slots.

In my source system, I have table that has a start time and and end time and then a column that designates how many appointment slots are available per 15 minute interval.

Example:

``````    Location | Start Time | End Time | Appointment Slots
A        | 9:00 am    | 5:00 pm  | 2
B        | 9:00 am    | 5:00 pm  | 1
``````

I have already been able to generate a list of 15 minute blocks using a CTE. However, each 15 minute slot just says that 2 slots are available for location A and 1 slot is available for location B. What I'd like is to be able to generate 2 rows per 15 minutes for location A and 1 row per 15 minutes for location B.

-

This is an example of when a "Number" table becomes very handy! If you are not familiar with this concept, read this blog post from Adam Machanic: You REQUIRE a Numbers Table

The numbers table is used to join to the Appointment Slots column (in this case) to duplicate each row AppointmentSlots number of times.

``````-- A simple recreation of your CTE data...
CREATE  TABLE #AppointmentPeriods
(
AppointmentLocation CHAR(1),
AppointmentStartTime TIME,
AppointmentEndTime TIME,
AppointmentSlotsAvailable SMALLINT
)

INSERT  INTO #AppointmentPeriods VALUES
('A', '09:00', '09:15', 2),
('A', '09:15', '09:30', 2),
('A', '09:30', '09:45', 2),
-- ....
('A', '16:30', '16:45', 2),
('A', '16:45', '17:00', 2),
('B', '09:00', '09:15', 1),
('B', '09:15', '09:30', 1),
('B', '09:30', '09:45', 1),
-- ....
('B', '16:30', '16:45', 1),
('B', '16:45', '17:00', 1)

-- Numbers table
CREATE  TABLE #Numbers (NumberValue SMALLINT)

INSERT  INTO #Numbers
-- TOP value should be changed so it is greater than the
-- maximum number of potential appointment slots any location can have
SELECT  TOP 20
ROW_NUMBER() OVER (ORDER BY object_id)
FROM    sys.objects

SELECT  #AppointmentPeriods.*, NumberValue AS AppointmentSlotNumber
FROM    #AppointmentPeriods
INNER JOIN #Numbers
ON AppointmentSlotsAvailable >= NumberValue
``````

Query output:

``````Location | StartTime | EndTime | AppointmentSlots | SlotNumber
A        | 09:00     | 09:15   | 2                | 1
A        | 09:00     | 09:15   | 2                | 2
A        | 09:15     | 09:30   | 2                | 1
A        | 09:15     | 09:30   | 2                | 2
B        | 16:30     | 16:45   | 1                | 1
B        | 16:45     | 17:00   | 1                | 1
``````

PS: There are many ways to generate a numbers table: SO: What is the best way to create and populate a numbers table?

-
Awesome! I knew there would be a handy way to do this. I'll be testing this out tomorrow and will let you know how it works. – njkroes Feb 21 '14 at 2:21
Works like a charm! I actually modified your approach by creating using a CTE to create a numbers table on the fly for the max appointment slots available and used that. Thanks! – njkroes Feb 21 '14 at 14:59