|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
February 13th, 2005, 10:42 AM
|
|||
|
|||
|
create running counter for SQL data (select)
for eg i got previous data such as
customer name --------- jim kim roy how do i create counter in the select statement, so that the result appears as no customer name --------------- 1 jim 2 kim 3 roy
|
|
#2
February 13th, 2005, 04:11 PM
|
||||
|
||||
|
Ok, here is some code that will achieve the desired result, however I am convinced that there is an easier way.
Code:
declare @Count numeric create table #tmpResultSet ( field1 numeric, field2 nvarchar(255)) insert into #tmpResultSet select '', [name] from someTable declare csrNameSet as cursor for select field1 from #tmpResultSet select @count=1 open csrNameSet while @@FETCH_STATUS <> 0 begin fetch next from csrNameSet into @name update #tmpResultSet set field1=@count where #tmpResultSet.Field2=@name select @count=@count+1 end close csrNameSet deallocate csrNameSet select * from #tmpResultSet drop table #tmpResultSet you might try turning field1 from #tmpResultSet into an ID field, that will count up with what ever increment you like. That will cut the code down to about 10 lines.
|
|
#3
February 13th, 2005, 11:25 PM
|
|||
|
|||
The simple one :
use yourDB go declare @temp table (id int identity(1,1) , name char(100)) insert @temp(name) select name from tblCustomerName select * from @temp The idea of this solution is to create temporary table with 2 fields, one field is identity field seed (1,1) another field is containing name from customer table.. i think this solution more simple and quick to load because it doesn't need any cursor that fetch each record. Regards, Tonny Soeroso
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > create running counter for SQL data (select) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
