January 16, 2015 at 3:12 am
I have column named HouseName in a table-discpline , that has got duplicate names i.e.
Test1
Test1
Test2
Test2
Test2
Test3
Test4
The expected output is(i.e. replace the names with *_1 ,*_2 and so on when the names are duplicating,Leave the unique names as it is)
Test1
Test1_1
Test2
Test2_1
Test2_2
Test3
Test4
January 16, 2015 at 3:24 am
This may help.
;WITH mytable AS
(SELECT * FROM (
VALUES('Test1'),('Test1'),('Test2'),('Test2'),('Test3'),('Test4')
)x(y))
, plusrownum AS
(SELECT row_number() OVER (PARTITION BY y ORDER BY y) AS rownum,y FROM mytable)
SELECT y + CASE WHEN plusrownum.rownum<>1 THEN '_' + cast(plusrownum.rownum-1 AS VARCHAR) ELSE '' END FROM plusrownum
January 16, 2015 at 4:16 am
Thanks. But i need an update script to Update the records directly in DataBase.
January 16, 2015 at 4:18 am
...then use the script I provided, and change the last select into an update.
January 16, 2015 at 4:21 am
Can you please modify the query to Update .Thanks !
January 16, 2015 at 4:46 am
Changing Select to Update doesn't work.Can you please post the UPDATE Query .Thanks !
January 16, 2015 at 5:20 am
I took the result set into temp table (that had name ,id) and then looped through its records(using while loop) and updated the original table. Thanks !
January 16, 2015 at 6:39 am
Try:
with CTE_RN as
(
select
*,
ROW_NUMBER() OVER(PARTITION BY HouseName ORDER BY HouseName) as RN
from discpline
)
update CTE_RN
set HouseName = HouseName + '_' + cast(RN - 1 as varchar)
where RN > 1
Hope this helps.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply