sql help - 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

sql help

Author

Topic

sqluser78
Starting Member

1 Post

Posted - 2011-04-21 : 05:44:38

dear all,

sql server verison is 2000

following is data structure and sample data

create table temp (s_no int, ref as varchar(10), counter smallint)

insert into temp values(1,'12305',4)
insert into temp values(2,'65509',3)
insert into temp values(3,'98601',1)
insert into temp values(4,'97607',5)
insert into temp values(5,'52205',2)

i want following output

s_no ref ref_counter
1 12305 12305
1 12305 12306
1 12305 12307
1 12305 12308
1 12305 12309
2 65509 65509
2 65509 65510
2 65509 65511
3 98601 98601
4 97607 97607
4 97607 97608
4 97607 97609
4 97607 97610
4 97607 97611
5 52205 52205
5 52205 52206

here counter can be from 1 to 99
and in out put i have to add this couter values to produce the desired result
how it can be possible

sqluser

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-04-21 : 06:42:31

Use a number/tally table.
The following uses master.dbo.spt_values but it would be better to have a tally table in your db.


SELECT T.s_no, T.ref, T.ref + N.number AS ref_counter
FROM [temp] T
JOIN master.dbo.spt_values N
ON N.type = 'P'	AND N.number < T.counter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources