May 24, 2011 at 12:32 pm
Please forgive the title; here's the deal.
I have table1 with the 100 rows; 1,2,3,4,5, etc...
table2 has 3 rows; '5/16/2011', '5/17/2011', and '5/18/2011'
I want my result set to look like this:
Number Date
1 5/16/2011
1 5/17/2011
1 5/18/2011
2 5/16/2011
2 5/17/2011
2 5/18/2011
3 etc...
This seems like it should be so simple, but I can't seem to figure it out using cross joins, inner joins, unions, whatever.
Your help is great appreciated!
May 24, 2011 at 12:38 pm
The CROSS JOIN is what you're looking for. Here's an example:
-- For information on how a tally table works, please see the article
-- 'The "Numbers" or "Tally" Table - What it is and how it replaces a loop'
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/
;WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS),
cteDates (MyDate) AS (SELECT '5/16/2011' UNION ALL SELECT '5/17/2011' UNION ALL SELECT '5/18/2011')
SELECT TOP (100) N, MyDate
FROM TALLY
CROSS JOIN cteDates
ORDER BY TALLY.N, cteDates.MyDate;
Results:
N MyDate
-------------------- ---------
1 5/16/2011
1 5/17/2011
1 5/18/2011
2 5/16/2011
2 5/17/2011
2 5/18/2011
3 5/16/2011
3 5/17/2011
3 5/18/2011
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 24, 2011 at 1:02 pm
Thank you! I'm not sure how I missed that cross join, but sometimes it takes askin' to get it.
Final query went like this
SELECT DISTINCT table1.Number, table2.Date
FROM table1 CROSS JOIN table2
ORDER BY Number,Date
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply