Splitting of Column Values

Question

hi everyone i am having the below input table INPUT Name Hari;shanker;singh Hari;shanker Hari need to get the output like below please let me know OUTPUT Name count Shanker 2 Hari 3 Singh 1 Thanks in Advance

Nitin Sontakke · Answer

You can if you are comfortable with dynamic query and using #table instead of @name. You can use @name as well but then entire thing should be done in dynamic query. For example: declare @dynamicSQL nvarchar(max) = ''; select @dynamicSQL += 'insert into #name select [split_value] from dbo.split(''' + name + ''' ';'); ' from data_table. create #name table execute sp_executeSQL @dynamicSQL select count query on #name table as before. Hope it helps!

Hari Shanker · Answer

Hi Nitin I need the query without using Cursor Thanks

Nitin Sontakke · Answer

Hello You can write / download script of a function which splits the text by given delimiter and put the values in a table. And then fire a simple count query. The untested sample code as follows: create @name table ( [name] varchar(100) ) cursor each row in a input table take column value in a string variable insert into @name select [split_value] from dbo.split(@column_value ';'); end cursor select [name] count(1) from @name group by [name] order by [name]