SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Increment a Counter in a SELECT		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

89 Posts
Posted - 12/04/2012 :  14:32:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/04/2012 :  14:41:28  Show Profile  Reply with Quote
How about you do with computed column.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/04/2012 :  14:42:39  Show Profile  Reply with Quote
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;
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/04/2012 :  15:05:28  Show Profile  Reply with Quote
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
Edited by - sodeep on 12/04/2012 15:08:14
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000