Increment by 10 Column Value in select Query

Question

Post reply

Increment by 10 Column Value in select Query

shankaran_sraj

SSC-Addicted

Points: 444
More actions
September 7, 2010 at 10:20 am

#387739

Hi everyone,
I having a table Tran_Id in that table two columns Id and Sno.
ID SNO
1 5124
2 6231
if i selected a particular Id's and Sno means i need the result like the below format
ID SNo
1 5124
1 5125
1 5126
1 5127
: :
: :
1 5133
Suppose i select the ID= 2 means I need SNo started from 6231 to 6240
Any help would be appreciated. Thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would use CROSS APPLY on a 10 row table (or subquery):

DECLARE @t TABLE(ID INT, SNO INT)

INSERT INTO @t

SELECT 1,5124 UNION ALL

SELECT 2,6231

SELECT t.ID , t.SNO + z.N

FROM @t t

CROSS APPLY

(

SELECT number N from master..spt_values WHERE type='P' and number<10

)z

ORDER BY t.ID,N

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 2

shankaran_sraj (9/7/2010)
Hi everyone,
I having a table Tran_Id in that table two columns Id and Sno.
ID SNO
1 5124
2 6231
if i selected a particular Id's and Sno means i need the result like the below format
ID SNo
1 5124
1 5125
1 5126
1 5127
: :
: :
1 5133
Suppose i select the ID= 2 means I need SNo started from 6231 to 6240
Any help would be appreciated. Thanks in advance.

What do you want to do if one of those calculated numbers already exists in the table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)