TheBugSlayer - This is very close to what I would suggest. My slight modification would be to change the old comma join to a cross join, like this...
Code:
INSERT INTO @v_results (location, drawnhour)
SELECT T24Field, T24Field
FROM CTE_Table4 [!]Cross Join[/!] CTE_Table24
Another difference would be how the table that contains 24 rows would be built. In every database I deal with, I always have a numbers table (some people call it a tally table). My Numbers table has just one column (int identity primary key) and has 1,000,000 rows. That may seem like a lot, but it only takes 4 megs to store it, and it comes in handy a lot. If you can't add this table to your database, there is a "poor man's" number table built in for you.
The code would look like this:
Code:
insert into @v_results (location, drawnhour)
select b.location, A.Number
from @v_locations b
Cross Join (
Select Number
From master..spt_values
Where Type = 'P'
And Number Between 1 And 24
) As A
Of course, you could make the poor man's number table be a cte if you wanted. I'm also not going to claim that a numbers table approach would give better performance than a recursive cte (although I suspect it would). What I will say about performance, is that this method should be many times faster than the nested loop approach.
briangriffin - This approach may not be exactly like yours based on the ordering of data. As such, you may need to play around with order by's to get code that is equivalent to your nested loop approach.
I strongly urge you to consider adding a numbers table to your database. It will help with this query and you're likely to find many other uses for it too.
If you don't have a numbers table and would like to add one:
Code:
/* ****** Creation of a Numbers tables ****** */
CREATE TABLE Numbers(Num INT IDENTITY(1,1) PRIMARY KEY)
go
SET NOCOUNT ON
BEGIN TRAN
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
INSERT INTO Numbers DEFAULT VALUES
COMMIT TRAN
go 100000
-
George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom