increment column - 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

SQL Server 2000 Forums

Transact-SQL (2000)

increment column

Author

Topic

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-14 : 09:34:26

if i have a table
TableA(col1 varchar(10))
with the following data:
dataA
dataB
....
dataJ

how can i create/get an autoicrement field next to the data in my select statement ?

the resultset should look like

id_____data
--------------
1______dataA
2______dataB
...
10_____dataJ

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-14 : 09:43:09

drop it into a temp table?


Create Table #tableA (col1 varchar(10))
Insert Into #tableA
Select 'dataA' Union All Select 'dataB' Union All Select 'dataC' Union All Select 'dataD'
Union All Select 'dataE' Union All Select 'dataF' Union All Select 'dataG'

Select * From #tableA

Create Table #tableB (id int identity(1,1), col1 varchar(10))
Insert Into #tableB (col1)
Select col1 From #tableA

Select * From #tableB

Drop Table #tableA
Drop Table #tableB

Corey

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-14 : 09:50:17

anything with a single query ? (or am i asking for too much ?) ;)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-14 : 10:04:28

voila!


Create table #tableA (col1 varchar(10))
Insert Into #tableA
Select 'dataA' Union All Select 'dataB' Union All Select 'dataC' Union All Select 'dataD'
Union All Select 'dataE' Union All Select 'dataF' Union All Select 'dataG'

Select rank=count(*), t1.* 
From #tableA t1, #tableA t2
where t1.col1 >= t2.col1
group by t1.col1

Drop Table #tableA

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-14 : 10:05:08

depends on the specific data... if each record has a column that is unique and that the data can be sorted...


Create table #tableA (col1 varchar(10))
Insert Into #tableA
Select 'dataA' Union All Select 'dataB' Union All Select 'dataC' Union All Select 'dataD'
Union All Select 'dataE' Union All Select 'dataF' Union All Select 'dataG'

Select * From #tableA

Select 
	id = (Select count(*) From #tableA Where col1<= A.col1),
	col1
From #tableA A

Drop Table #tableA

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-14 : 10:05:56

[snipped]

Corey

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-15 : 01:35:26

thx a lot ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources