Create multi sequenced recordset as function of field value

[Topkapi]

Apologies if this is has a simple solution but I'm still in the early stages of learning T-SQL.
I have a table that contains package tracking numbers and a 'boxes' column that contains the numbers of packages covered by a tracking number thus

trackingno boxes
---------- -----
911111111 4
911111112 2
911111113 3

I would like to create a query that will give me an output like

trackingno boxnumber
---------- -----
911111111 1
911111111 2
911111111 3
911111111 4
911111112 1
911111112 2
911111113 1
911111113 2
911111113 3

I've been wrestling with this for hours now and have 'Googled' but found nothing similar so any help/guidance/pointers would be greatly appreciated.

TIA

 

[Juice05]

Do you have another table that has details about each of the boxes?

 

[JamesLean]

See if this helps you:

[green]/************************************
	Test data
************************************/[/green]

DECLARE @t table (
	trackingno int,
	boxes int
)

INSERT @t VALUES (911111111, 4)
INSERT @t VALUES (911111112, 2)
INSERT @t VALUES (911111113, 3)

[green]/************************************
	Query
************************************/[/green]

DECLARE @max int,
	@ctr int

DECLARE @nums table (num int)

SELECT @max = MAX(boxes)
FROM @t

SET @ctr = 1
WHILE @ctr <= @max
BEGIN
	INSERT @nums VALUES (@ctr)
	SET @ctr = @ctr + 1
END

SELECT t.trackingno, n.num AS boxnumber
FROM @t t CROSS JOIN @nums n
WHERE n.num <= t.boxes
ORDER BY t.trackingno, n.num

--James

 

[Topkapi]

Juice05 - no I have no other table unfortunately.

James - Thanks for the help, that worked a treat!
I've never used cross joins before and am reading up on their application.