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]

Splitting of Column Values

Answers (3)