Sometimes there would
be a requirement to add serial number (or) row number to the result set.
Example: The requirement would be to select some columns from the database tables along with Serial Number like below
SNo
|
Customer_Name
|
Customer_Phone_Number
|
1
|
aaa
|
xxx-xxx-xxxx
|
2
|
bbb
|
xxx-xxx-xxxx
|
So, in this post we
will see how we can select that using T-SQL on Microsoft SQL Server 2008
We can achieve this in
several ways:
1) Using Row_Number()
Row_Number() Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
T-SQL Script:
SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID) AS [S.No]
,CUSTOMER_NAME
,CUSTOMER_PHONE_NUMBER
FROM database.dbo.tblTableName
2) Using Identity(Property)
Identity() property creates an identity column in the table. So, first we need to create a temporary table with [S.No] column on it and then we will select the required columns from the temporary table.
T-SQL Script:
SELECT IDENTITY(INT,1,1) AS [S.No]
,CUSTOMER_NAME
,CUSTOMER_PHONE_NUMBER
INTO #tempTable
FROM database.dbo.tblTableName
--Now, the result set
SELECT [S.No]
,CUSTOMER_NAME
,CUSTOMER_PHONE_NUMBER
FROM #tempTable
1) Using Row_Number()
Row_Number() Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
T-SQL Script:
SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID) AS [S.No]
,CUSTOMER_NAME
,CUSTOMER_PHONE_NUMBER
FROM database.dbo.tblTableName
2) Using Identity(Property)
Identity() property creates an identity column in the table. So, first we need to create a temporary table with [S.No] column on it and then we will select the required columns from the temporary table.
T-SQL Script:
SELECT IDENTITY(INT,1,1) AS [S.No]
,CUSTOMER_NAME
,CUSTOMER_PHONE_NUMBER
INTO #tempTable
FROM database.dbo.tblTableName
--Now, the result set
SELECT [S.No]
,CUSTOMER_NAME
,CUSTOMER_PHONE_NUMBER
FROM #tempTable
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757