how to expand table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to expand table

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 10:36:58

I have a table t1
id maxday
1 4
2 6
3 3
4 10
5 10000

I want to expand it to t2
id d maxday
1 0 4
1 1 4
1 2 4
1 3 4
1 4 4
2 0 6
2 1 6
2 2 6
2 3 6
2 4 6
2 5 6
2 6 6
3 0 3
3 1 3
3 2 3
3 3 3
4 0 10
------

How can I do that? Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 10:45:16

are you using SQL Server 2000 or 2005 ?

jeff06
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 10:49:59

version 8.00
should be 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 10:53:02

[code]
declare @table table
(
id int,
maxday int
)

insert into @table
select 1, 4 union all
select 2, 6 union all
select 3, 3 union all
select 4, 10 union all
select 5, 10000

declare @max int
select @max = max(maxday) from @table

select t.id, n.NUMBER as d, t.maxday
from @table t inner join F_TABLE_NUMBER_RANGE(0, @max) n
on n.NUMBER <= t.maxday
order by t.id, d

-- for SQL Servr 2005 only
select t.id, n.NUMBER as d, t.maxday
from @table t cross apply F_TABLE_NUMBER_RANGE(0, t.maxday) n
order by t.id, d
[/code]

F_TABLE_NUMBER_RANGE is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

jeff06
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 10:58:34

Thanks you for your reply
I got following error msg after run the code.

Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'F_TABLE_NUMBER_RANGE'.
how can I fix this problem?
Thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 11:03:01

quote:
Originally posted by jeff06

Thanks you for your reply
I got following error msg after run the code.

Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'F_TABLE_NUMBER_RANGE'.
how can I fix this problem?
Thanks again

Yes.

quote:
F_TABLE_NUMBER_RANGE is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 height="1" noshade id="quote">

jeff06
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 11:17:45

I tried to use this code
CREATE FUNCTION xf_TableNumberRange(@nMin INT, @nMax INT)
RETURNS @t TABLE (number INT) AS
BEGIN
DECLARE @i INT
SET @i = @nMin
WHILE @i <= @nMax
BEGIN
INSERT INTO @t (number) VALUES (@i)
SELECT @i = @i + 1
END
RETURN
END

The problem is that I have no rigth to create function
can You give me some more suggestions? Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-13 : 11:36:44

Create a table with numbers from 1 to 100 and do join with it instead of the function

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 11:36:58

can you create a table ? then create a tally table

jeff06
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 12:32:52

THX for all suggestions!