Increment a Counter in a SELECT

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

Increment a Counter in a SELECT

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-12-04 : 14:32:35

I need to create a value in a column that increments.

A simple example would be:


SELECT

SomeColumn as "Some Column",
'N2-L2' AS "Plan vs Instance"

FROM SomeTable

The key is, the number "2" (in 'N2-L2') needs to increment.

So, the value in the first selected row would be:

'N2-L2'

The value in the second selected row would be:

'N3-L3'

... and so on.

What is the best way to accomplish this?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 14:41:28

How about you do with computed column.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-04 : 14:42:39

There is a discussion in this article: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

One approach would be to create a computed column as in the example below:

CREATE TABLE #tmp 
(
	someCol VARCHAR(32), 
	id INT IDENTITY (1,1), 
	PlanVInstance AS 'N'+CAST(id AS VARCHAR(32))+'-L'+CAST(id AS VARCHAR(32))
);

INSERT INTO #tmp (someCol) VALUES ('abcd');
INSERT INTO #tmp (someCol) VALUES ('efgh');

SELECT somecol , PlanVInstance FROM #tmp;

DROP TABLE #tmp;

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 15:05:28

If you are trying to update or not use computed column

Update T
Set PlanVInstance = 'N' + cast(seq as Varchar(10))+ '-L' + cast(seq as Varchar(10))
from Table T
inner join
(
Select Id,ROW_NUMBER() OVER(ORDER by ID) as seq
from Table
)P on P.Id = T.Id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources