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

Go

 

How to count item values comma-separated values in SQL SERVER How to count item values comma-separated values in SQL SERVER Reviewed by NEERAJ SRIVASTAVA on 8:25:00 PM Rating: 5

No comments:

Powered by Blogger.