Help with T-SQL query

[Homerboy]

I have a query that returns 200 unique numbers from a table.

Number
--------
4
23
29
62
65
74
89
151
299
...
312

I need to add a column so that the past 10 years is represented for each of those unique numbers (so I will have 2000 rows total: 10x200)

Year	Number
2013	4
2012	4
2011	4
2010	4
2009	4
2008	4
2007	4
2006	4
2005	4
2004	4
2013	23
2012	23
2011	23
2010	23
2009	23
2008	23
2007	23
2006	23
2005	23
2004	23
...
2013	299
2012	299
2011	299
2010	299
2009	299
2008	299
2007	299
2006	299
2005	299
2004	299

Does that make sense?
I feel like this is really obvious and I've just over thought the problem at this point and can't wrap my brain back around it.

Thanks in advance.

 

[Homerboy]

Small addendum to this.

A) it's not really 10 years (it's since 2000, but I can make it a fixed 10 years if need be)
B) I've got my code below. I started down the path of a CTE, but realized that was a dead end.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = '2000-01-01'
SET @endDate = GETDATE()
    
;with

forwarders (forw_no)
as
(select forw_no from MASFORW where ADDR is not null
)
,

dates(Date) 
as 
(SELECT 
@startdate as Date
UNION ALL
SELECT DATEADD(y,1,[Date])
FROM dates 
WHERE DATE < @enddate
)


select 
forwarders.forw_no
from forwarders

 

[Ancalagon44]

Construct a temporary table A with one column - year - stored as an integer. Fill it with your 10 years.

Then, select from A and your query that returns the 200 unique numbers, but do so using a cross join. That will result in 2000 results.

 

[JustAnAverageGuy]

Ancalagon44 Is correct. You're looking for a cross join.
A SQL Server sample since source tables aren't available.

;WITH TenYears as
(
SELECT top 10 YEAR(DATEADD(YEAR, -ROW_NUMBER() OVER (ORDER BY o.Object_id) + 1, GETDATE())) [Year]
FROM sys.all_objects o
),
TwoHundredNums as
(
SELECT top 200 o.object_id Number
FROM sys.all_objects o
)

SELECT	TenYears.[Year],
	TwoHundredNums.Number
		
FROM TenYears
CROSS JOIN TwoHundredNums
ORDER BY Number, Year DESC

 

[KLin]

1. declare a table variable and include year field
2. insert records into table variable for last 10 years
3. do a select statement and include both tables in from clause just using a comma(should act as a cross join)