Creating Continuous Row Number in a column

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

SQL Server 2008 Forums

Transact-SQL (2008)

Creating Continuous Row Number in a column

Author

Topic

neo_bagsjol
Starting Member

6 Posts

Posted - 2014-02-03 : 22:25:02

hello guys

good day, i would like to ask help for this issue/problem that i've never found an answer here's the problem i have a column Month and the data type is integer in my table tblLoan when the user inputted 5 months then i will create 5 rows starting from 1 to 5 and save to the table then second the user again inputted 4 months so it will continue creating rows number to 6 to 9 months as shown below.

Months
1
2
3
4
5 -- for 5 months inputted on the table
6 -- continue for the 4 months inputted
7
8
9

how can i achieve this output

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-03 : 22:53:35

[code]
declare @start_month int,
@months int

select @start_month = 1,
@months = 6

select @start_month = max( [months] ) + 1
from the_table

;with
mth as
(
select [months] = isnull(@start_month, 1)
union all
select [months] = [months] + 1
from mth
where [months] <= isnull(@start_month, 1) + @months - 2
)
insert into the_table ( [months] )
select [months]
from mth
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-04 : 06:31:35

[code]
insert into the_table ( [months] )
select (select max([months])
from mth) + row_number() over (order by val)
from @userinput
[/code]

assuming @userinput is table containing user input values.
if no such table then specfy what and how will user values get input?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources