Adding A "Counter" Column ?

• Bilster

  SSC Veteran

  Points: 296

  More actions

  September 27, 2002 at 3:27 pm

  #158835

  Hello-

  Anyone know a way (without cursors) to add a "Counter" column to a ResultSet ??

  Select ?? As Counter, Name From People

  Counter Name

  ------- ------

  1 Jane

  2 Joe

  3 Jack

  I guess it would have to be efficient as well

  

• Rodrigo Acosta

  SSC-Insane

  Points: 21261

  More actions

  September 27, 2002 at 5:00 pm

  #437425

  Try:

  Declare @Select Varchar(500)

  Set @Select='Select c1, c2, c3 from t1'

  Create Table #temp

  (id int identity (1,1) not null,

  c1,

  c2,

  c3)

  Insert #temp

  EXEC (@Select)

  Select * from #Temp

• MikeAngelastro

  SSC Enthusiast

  Points: 190

  More actions

  September 30, 2002 at 12:06 am

  #437471

  Hi,

  I don't know if you can do this with T-SQL code but you may want to create a temp table with an identity field. When you fill the table with the results of the query, the identiy field will automatically increment.

  Mike

• skylon

  SSC Veteran

  Points: 243

  More actions

  September 30, 2002 at 9:51 am

  #437503

  Try this:

  SELECT IDENTITY(int, 1,1) AS [ID], blah0, blah1, blah2 INTO xTable

  FROM blah_table

  This will create the table xTable with your result set and an identity column. You can then select from this table and then drop it when done.