Thursday, 16 February 2017

How to Add the Row Index with Partition by Column on Existing Table in SQL Server

SQL Server Query to Add the Row Index with Partition by Column on Existing Table
We can do this by using the Common Table Expression (CTE's) . 
Before that you have to create a blank Row_Id column where we want to add the Row_Index

With CTE_RowIndex as
(
Select*,
New_Row_Id= ROW_NUMBER() OVER(PARTITION BY Dept_Name ORDER BY Dept_Name) from Tbl_RowIndex

)
Update CTE_RowIndex set Row_Id=New_Row_Id

Select*from Tbl_RowIndex

;

Lets suppose we have a Table with no values in Row_Id column, in which we want to update the Row_Index Partition by Dept_Name , as follows 

Alter Table Tbl_RowIndex Add Row_Id Int

Select* from Tbl_RowIndex


Next run the above  Query, which will add the Row_Index Partition by Dept_Name into the Row_Id field. The output as follows :


Happy Learning..
Tamatam


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts