Count the number of instances where value in comma separated list-CodePudding

I have a table that holds comma separated values;

Column1
--------------------
ABC 21.17
ABC 21.17, ABC 21.18
ABC 21.16, ABC 21.17

And I am trying to count by each comma delimited value, so for example;

Column1           Count
----------        ----------
ABC 21.16         1
ABC 21.17         3
ABC 21.18         1

CodePudding user response：

If you're on SQL Server 2013 (Compatibility Level 130) or above, you can use STRING_SPLIT with a CROSS APPLY:

--Create table variable with some test data
declare @inputData table (Column1 varchar(max))
insert into @inputData values ('ABC 21.17'),('ABC 21.17, ABC 21.18'),('ABC 21.16, ABC 21.17')

select trim(value) as Column1, count(*) as [count]
from @inputData
cross apply string_split(Column1, ',')
group by trim(value)

Which gives your desired output:

/-------------------\
|  Column1  | count |
|-----------|-------|
| ABC 21.16 |   1   |
| ABC 21.17 |   3   |
| ABC 21.18 |   1   |
\-------------------/