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:
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
HI :)
How could i amend the above code to out put a serial number such as
000000001, instead of '1'
thanks keith
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 !!!
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
hey got it workin :)
SELECT lpad(ROW_NUMBER() OVER (ORDER BY CLAIM_NO),6,'0') as Snum, INPUT_FORENAME FROM lpa_input
Thanks
Post a Comment