sql - Numbering Duplicated names in comma-separated row -

i stuck in query , have no idea do. need number every duplicate name in comma-separated row

for example in attached picture, row 7th has midazolam 2 times. in case first name should midazolam(1) , second should midazolam(2) , on. possible using sql query somehow?

here query generate dummy database , data:

use [master] go /****** object:  database [dummydatabase]    script date: 10/5/2015 12:42:30 pm ******/ create database [dummydatabase]  containment = none  on  primary  ( name = n'dummydatabase', filename = n'c:\program files\microsoft sql server\mssql11.sqlexpress\mssql\data\dummydatabase.mdf' , size = 30720kb , maxsize = unlimited, filegrowth = 1024kb )  log on  ( name = n'dummydatabase_log', filename = n'c:\program files\microsoft sql server\mssql11.sqlexpress\mssql\data\dummydatabase_log.ldf' , size = 16576kb , maxsize = 2048gb , filegrowth = 10%) go alter database [dummydatabase] set compatibility_level = 110 go if (1 = fulltextserviceproperty('isfulltextinstalled')) begin exec [dummydatabase].[dbo].[sp_fulltext_database] @action = 'enable' end go alter database [dummydatabase] set ansi_null_default off  go alter database [dummydatabase] set ansi_nulls off  go alter database [dummydatabase] set ansi_padding off  go alter database [dummydatabase] set ansi_warnings off  go alter database [dummydatabase] set arithabort off  go alter database [dummydatabase] set auto_close off  go alter database [dummydatabase] set auto_create_statistics on  go alter database [dummydatabase] set auto_shrink off  go alter database [dummydatabase] set auto_update_statistics on  go alter database [dummydatabase] set cursor_close_on_commit off  go alter database [dummydatabase] set cursor_default  global  go alter database [dummydatabase] set concat_null_yields_null off  go alter database [dummydatabase] set numeric_roundabort off  go alter database [dummydatabase] set quoted_identifier off  go alter database [dummydatabase] set recursive_triggers off  go alter database [dummydatabase] set  disable_broker  go alter database [dummydatabase] set auto_update_statistics_async off  go alter database [dummydatabase] set date_correlation_optimization off  go alter database [dummydatabase] set trustworthy off  go alter database [dummydatabase] set allow_snapshot_isolation off  go alter database [dummydatabase] set parameterization simple  go alter database [dummydatabase] set read_committed_snapshot off  go alter database [dummydatabase] set honor_broker_priority off  go alter database [dummydatabase] set recovery simple  go alter database [dummydatabase] set  multi_user  go alter database [dummydatabase] set page_verify checksum   go alter database [dummydatabase] set db_chaining off  go alter database [dummydatabase] set filestream( non_transacted_access = off )  go alter database [dummydatabase] set target_recovery_time = 0 seconds  go use [dummydatabase] go  go /****** object:  table [dbo].[testtable]    script date: 10/5/2015 12:42:31 pm ******/ set ansi_nulls on go set quoted_identifier on go set ansi_padding on go create table [dbo].[testtable](      [drugname] [text] null, ) on [primary] textimage_on [primary] insert [dbo].[testtable] ([drugname]) values ('midazolam, ranitidine, midazolam, propofol, cephazolin, lignocaine, propofol, propofol, fentanyl, fentanyl, oxygen, isoflurane, oxygen, isoflurane, oxygen, isoflurane, oxygen, isoflurane, oxygen, nitrous ox, oxygen, nitrous ox, isoflurane, oxygen, nitrous ox, plasmalyte, plasmalyte, plasmalyte, plasmalyte, isoflurane, oxygen, isoflurane') insert [dbo].[testtable] ([drugname]) values ('midazolam, ranitidine, midazolam, propofol, cephazolin, lignocaine, propofol, propofol, fentanyl, fentanyl, oxygen, isoflurane, oxygen, isoflurane, oxygen, isoflurane, oxygen, isoflurane, oxygen, nitrous ox, oxygen, nitrous ox, isoflurane, oxygen, nitrous ox, plasmalyte, plasmalyte, plasmalyte, plasmalyte, isoflurane, oxygen, isoflurane')  use [master] go alter database [dummydatabase] set  read_write  go 

ultimately, storing list of values in single field create headaches. solution, if don't want store values in normalized fashion, split listed values separate rows. use xml functionality split string separate rows, apply row_number() , count() change names desired. using xml functionality again rebuild lists. if want order of names in lists preserved you'll have add numbering use in order by:

;with  cte (select rtrim(ltrim(split.a.value('.', 'varchar(100)'))) txt,id                     (select cast ('<m>' + replace(drugname, ',', '</m><m>') + '</m>' xml) data                             ,id                       #test                      ) cross apply data.nodes ('/m') split(a))       ,cte2 (select *,cast(row_number() over(partition id,txt order id,txt)as varchar(50)) rn                         ,count(*) over(partition id,txt) dup_ct                 cte                 ) select distinct id,stuff((select distinct ',' +   case when dup_ct > 1 txt+'('+rn+')' else txt end                                     cte2                                     a.id = b.id                                     xml path(''), type).value('.', 'varchar(max)')                                      ,1,1,'')   cte2 b 

note: i'm assuming have id field along each string, if not you'll need add 1 solution, can differentiate between rows after separation.

demo: sql fiddle