How to count item values comma-separated values in SQL SERVER

We have a table like below:

IN this Picture, we want to count item values separated with commas like the below image:

(Count of items in a comma-separated column in a table)

To get this type we can two methods:

First Method:

SQL server:

SELECT service, COUNT(*) AS [Count]

FROM (SELECT Split.a.value('.', 'NVARCHAR(MAX)') service

FROM (SELECT CAST('<X>'+REPLACE(service, ',', '</X><X>')+'</X>' AS XML) AS String

from tblname1

) AS A

CROSS APPLY String.nodes('/X') AS Split(a)) AS O

GROUP BY service

Second Method:

We can use CROSS_APPLY with STRING_SPLIT to create rows from the comma-separated data values, and then COUNT the occurrences of each value:

STRING_SPLIT : A table-valued function that splits a string into rows of substrings, based on a specified separator character.

SQL server:

SELECT service, COUNT(*) AS [Count]

FROM tblname1

CROSS APPLY STRING_SPLIT(service, ',')

GROUP BY service

Note:

Invalid object name ‘STRING_SPLIT’.

Compatibility level 130

STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function. So if you want to use this function, change the compatibility level to SQL SERVER 2016 or a higher version.

Run the below-given query to change the compatibility level of the database:

SQL server:

ALTER DATABASE databasename

SET COMPATIBILITY_LEVEL = 130