Individual count of text from a column

Guruprakash C

9y1.3k

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:

the result must be:

Please suggest your queries to get my desired result.

Thank in Advance.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Guruprakash C9y ago
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 Patel9y ago
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).