Individual count of text from a column

Question

Hai I need to display count of each text occurrence on all rows from a particular column. See result below: Here I have a set of Tags I need to display count each tag as column in the name 'Total' What I have done is: DECLARE @tags VARCHAR (8000) DECLARE @tot INT select @tags = coalesce (@tags + ''  ' ' ) + Labels from addNew select @tot = count (@tags) select a.Labels as Tags@tot as Total from addNew a inner join addNew n on a.Labels = n.Labels group by a.Labels the result must be: Please suggest your queries to get my desired result. Thank in Advance.

Guruprakash C · Answer

Finally I got what I required. CREATE FUNCTION [dbo].StringSplit ( @Labels varchar (8000) ) RETURNS @RESULT TABLE (Value VARCHAR (8000)) AS BEGIN DECLARE @SeparatorPosition INT = CHARINDEX( ''  @Labels ) @Value VARCHAR (8000) @StartPosition INT = 1 IF @SeparatorPosition = 0 BEGIN INSERT INTO @RESULT VALUES (@Labels) RETURN END SET @Labels = @Labels + '' WHILE @SeparatorPosition > 0 BEGIN SET @Value = SUBSTRING (@Labels  @StartPosition @SeparatorPosition- @StartPosition) IF( @Value '' ) INSERT INTO @RESULT VALUES (@Value) SET @StartPosition = @SeparatorPosition + 1 SET @SeparatorPosition = CHARINDEX( ''  @Labels  @StartPosition) END RETURN END Go select (fn.Value+ '(' + CONVERT ( varchar (100) count (fn.Value))+ ')' ) as TagCount from addnew a cross apply STRINGSPLIT(a.Labels) as fn group by fn.Value order by TagCount; Go

Tapan Patel · Answer

what SQL Server are you using? Is it 2016? If yes then you would be abel to use split function. If not then probably CLR function is your option (other than some crazy query).