Counter added to a SET, is it possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Counter added to a SET, is it possible?

Author

Topic

gmetaj
Starting Member

33 Posts

Posted - 2005-10-05 : 10:23:58

Hello all, i have a question i've beenk thinking for a little bit and cant seem to come up with a solution. What i want to do is add a unique counter to a resultset in query. For example

SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM AnyCombinationOfJoinsOrTables
WHERE SomeConditionIsTrue

The query above will produce a resultest of 4 columns.

'A1', 'B1', 'C1', 'D1'
'A2', 'B2', 'C2', 'D2'
'A3', 'B3', 'C3', 'D3'
.....
'An', 'Bn', 'Cn, 'Dn'

Now what i want to do is add a unique identifier to every row. The easiest way would be to add a counter integer so the resultset would look like

1, 'A1', 'B1', 'C1', 'D1'
2, 'A2', 'B2', 'C2', 'D2'
3, 'A3', 'B3', 'C3', 'D3'

.....
n, 'An', 'Bn', 'Cn, 'Dn'

Is there a way to achieve this in T-SQL without using cursors.

Thanks,

Gent

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-05 : 10:26:41

Store the result in a temp table with an identity column...

create table #tempBlah
(
id int identity(1,1) not null,
col01 varchar(100) null,
col02 varchar(100) null,
col03 varchar(100) null,
col04 varchar(100) null
)

Insert Into #tempBlah
Select col01, col02, col03, col04 From <yourQuery>

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

gmetaj
Starting Member

33 Posts

Posted - 2005-10-05 : 10:54:59

That seems pretty slick. A table variable would probably be faster in my opinion. It would add a little overhead to my query but will guarantee a unique id.

Thanks,

Gent

quote:
Originally posted by Seventhnight

Store the result in a temp table with an identity column...

create table #tempBlah
(
id int identity(1,1) not null,
col01 varchar(100) null,
col02 varchar(100) null,
col03 varchar(100) null,
col04 varchar(100) null
)

Insert Into #tempBlah
Select col01, col02, col03, col04 From <yourQuery>

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-05 : 10:55:40

I don't think a table variable can have an identity column... but I could be wrong

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 11:04:10

quote:
Originally posted by Seventhnight

I don't think a table variable can have an identity column... but I could be wrong

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Yes you are wrong

Declare @t table(i int identity(1,1), name varchar(10))
insert into @t(name) values('test')
insert into @t(name) values('test1')
Select * from @t

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-05 : 14:53:45

Awesome!

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources