minakshi_23

That worked! Cheers!

I also want to count the occurences of each word in that row.

Thanks

To find  the occurence of each word in each row please create the following table valued function in database

Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
	declare @idx int
	declare @slice varchar(8000)
    
	select @idx = 1
		if len(@String)<1 or @String is null  return
    
	while @idx!= 0
	begin
		set @idx = charindex(@Delimiter,@String)
		if @idx!=0
			set @slice = left(@String,@idx - 1)
		else
			set @slice = @String
		
		if(len(@slice)>0)
			insert into @temptable(Items) values(@slice)

		set @String = right(@String,len(@String) - @idx)
		if len(@String) = 0 break
	end
return
end

After that Call below query, here i have covered all aspect probabily.

Declare @Table Table
(
	Id int,
	Original varchar(50)
)
insert into @Table
Select 1,'vitA,vitB'
Union All
Select 2,'vitB'
Union All
Select 3,'vitC'
Union All
Select 4,'Protein,VitA,VitB'


Select Id,Original,COUNT(items) As RowWordCount ,
(
	select stuff(
	(
			SELECT ',' + Original
		FROM @Table
		FOR XML PATH('')
	),1,1,'') ) As KeyVal
From @Table
Cross Apply dbo.Split(Original,',')
Group by Id,Original
order by Id

---output
---------------------------------------------------------------
--Id	Original	RowWordCount	KeyVal
--1	vitA,vitB	2	vitA,vitB,vitB,vitC,Protein,VitA,VitB
--2	vitB	1	vitA,vitB,vitB,vitC,Protein,VitA,VitB
--3	vitC	1	vitA,vitB,vitB,vitC,Protein,VitA,VitB
--4	Protein,VitA,VitB	3	vitA,vitB,vitB,vitC,Protein,VitA,VitB