Thursday, March 15, 2007

Create Serial Number Column using SQL Server

       There is a common requirement to show records with serial number in Grid.  The common scenario is, we take template field and label in Item Template. In Row_DataBound we can generate serial number using variable which is increamnted each time. The another approach is to genarate this column in SQL itself. Yes you can do that..! Here is a Query for that.

SELECT ROW_NUMBER()  OVER (ORDER BY  ColumnName1) As SrNo, ColumnName1,  ColumnName2 FROM  TableName

Fig- (1) Query to display Serial Number Column using SQL Server 

Happy Programming !

5 comments:

Raja said...

Hi, To implement this in SQL 2000, you can use the trick shown in this article.

http://www.mutuallybeneficial.com/index_files/RowNumber_2005.htm

keithmck said...

HI :)

How could i amend the above code to out put a serial number such as
000000001, instead of '1'

thanks keith

Raja said...

Keith,

You can do this with the help of RIGHT function in SQL Server. Lets assume that you want that the serial number field should be 10 digits long. So you have to use following query,

SELECT RIGHT('0000000000' + cast(ROW_NUMBER() OVER (ORDER BY adminuserid) as varchar(10)),10) As SrNo FROM TableName

Happy Programming !!!

keithmck said...

HI Raja :)

Thanks for helping me:

Here is my code

SELECT RIGHT ('0000000000' + cast(ROW_NUMBER() OVER (ORDER BY CLAIM_NO) as varchar(10)),10) As SrNo, INPUT_FORENAME FROM lpa_input


But it doesnt seem to work :(

Im quite new to sql but iam using toad!!! (not sure if this makes any difference??))

Thanks Keith

keithmck said...

hey got it workin :)

SELECT lpad(ROW_NUMBER() OVER (ORDER BY CLAIM_NO),6,'0') as Snum, INPUT_FORENAME FROM lpa_input

Thanks