How to count the words in a column and group them together in SQL Server-CodePudding

I am struggling to find a way to split the words within a column in a column into multiple rows and count the occurrences.

Looking for some advice on possible ways to achieve requirement.

Table:

Internally I wanted to split the words into multiple rows:

Then finally group them to count the duplicates:

CodePudding user response：

If you are using SQL Server 2016 or later, we can take advantage of STRING_SPLIT here:

WITH cte AS (
    SELECT value AS A
    FROM yourTable
    CROSS APPLY STRING_SPLIT(ColumnA, ',')
)

SELECT A, COUNT(*) AS cnt
FROM cte
GROUP BY A;

Demo